Dynamic Sql Insert Update Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Sql Insert Update Question

Author

Topic

jspurlin
Starting Member

5 Posts

Posted - 2004-09-13 : 14:07:04

I need to use a dynamic sql statement to update module settings. Using IBuySpy as an example, I am using ModuleSettings for different classes of data: News has its own module settings table, classifieds has its own module settings, and so on.

The tables have the same columns ModuleId, SettingName and SettingValue. Then I have a table that is a child table of Module Definitions. In this table, the primary key table name is stored as a string as well as the module settings table. This is all an effort to create homogenous routines for handling updating and deleting information related to modules so that no records are orphaned ( step two is to provide the option to keep or archive this information.)

Step one is my problem. Here is the stored procedure for UpdateModuleSetting_Dynamic where the table name belonging to the correct category of data (news, classified, ads, etc) is passed to the procedure:

CREATE PROCEDURE UpdateModuleSettings
(
@TableName nvarchar(128),
@ModuleId int,
@SettingName nvarchar(50),
@SettingValue nvarchar(256)
)
AS

Declare @SQL nvarchar(1200)

SELECT @SQL = 'IF NOT EXISTS '
SELECT @SQL = @SQL + '(SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''')'

SELECT @SQL = @SQL + 'INSERT INTO ' + @TableName + ' (ModuleId, SettingName, SettingValue) VALUES '
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ','' ' +
CONVERT(NVARCHAR,@SettingName) + ''', ''' + CONVERT(NVARCHAR,@SettingValue)
+ ''') '

SELECT @SQL = @SQL + 'ELSE UPDATE ' + @TableName
SELECT @SQL = @SQL + ' SET SettingValue = ' + CONVERT(NVARCHAR,@SettingValue)
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''''

Exec ( @SQL)
GO

When I run the code I get an 'Invalid Column Name' error.

For example, if I pass these parameters (@TableName = News_ModuleSettings, @SettingName = 'xmlsrc', @SettingValue = 'c:\\xmlfile.xml'

I get the error 'xmlsrc' is not a valid column name. Likewise, @SettingValue throws the error 'c:\\xmlfile.xml' is not a valid column.

Here is the second problem, I can correctly retrieve information based on the ModuleId parameter, but the statement

SELECT @SQL = 'IF NOT EXISTS '
SELECT @SQL = @SQL + '(SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''')'

does not work. In other words, an empty record is return.

So if the ModuleId=58 the record is correctly return. If I want to check first to see if the SettingName is unique, an empty recordset is returned using the same syntax (minus IF EXISTS);

SELECT @SQL = @SQL + '(SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''')'

I am brand new to Dynamic Sql in a stored procedure.

Windows XP PRO Service Pack 2
SqlServer 2000 (Developer Edition) Service Pack 3a

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-13 : 14:54:34

SELECT @SQL = @SQL + ' SET SettingValue = ' + CONVERT(NVARCHAR,@SettingValue)
s.b.
SELECT @SQL = @SQL + ' SET SettingValue = ''' + CONVERT(NVARCHAR,@SettingValue) + ''''

Can't see where the settingname error is.

When you insert you have a space before settingname chich may be why you can't find it.
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ','' ' +
s.b.
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ',''' +

By the way - whatever you are trying to do it's probably not a good idea.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jspurlin
Starting Member

5 Posts

Posted - 2004-09-13 : 15:14:13

May the heavens bless you. That was it.

Here what I think will be the last serious issue:

The IF NOT EXISTS statement is not working:

CREATE PROCEDURE UpdateModuleSettings
(
@TableName nvarchar(128),
@ModuleId int,
@SettingName nvarchar(50),
@SettingValue nvarchar(256)
)
AS

Declare @SQL nvarchar(1200)

//IF NOT EXISTS IS NOT DETECTING A MATCHING ModuleId WITH SettingName
//DUPLICATE RECORDS ARE BEING INSERTED
//-------------------------------------
SELECT @SQL = 'IF NOT EXISTS '
SELECT @SQL = @SQL + '(SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''')'
//-------------------------------------

//OR IS THIS STATEMENT SOMEHOW NOT UNDERSTOOD TO BE A PART OF THE
//IF NOT EXISTS CLAUSE?
//-------------------------------------
SELECT @SQL = @SQL + 'INSERT INTO ' + @TableName + ' (ModuleId, SettingName, SettingValue) VALUES '
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ','' ' +
CONVERT(NVARCHAR,@SettingName) + ''', ''' + CONVERT(NVARCHAR,@SettingValue)
+ ''') '
//-------------------------------------

SELECT @SQL = @SQL + 'ELSE UPDATE ' + @TableName
SELECT @SQL = @SQL + ' SET SettingValue = ''' + CONVERT(NVARCHAR,@SettingValue) + ''''
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''''

Exec ( @SQL)
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-13 : 19:13:36

It's caused by the second thing I pointed out

SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ','' ' +
s.b.
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ',''' +

The extra space is causing a different name to be inserted from that which is being checked.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jspurlin
Starting Member

5 Posts

Posted - 2004-09-13 : 22:50:22

Thank you for your help. I am new to anything besides basic T-SQL. I cannot thank you enough. My plate is full enough learning the .NET Framework, but I gotta tell ya database programming is "the bomb" and I can only program as effectively as my database design is effective.

A mentor/programmer once told me "learn database programming and that will be your ticket". Working on my own I can tell you, if the database is "done", programming is a snap.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources