A tiny attempt at making a dynamic update with loc

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

General SQL Server Forums

New to SQL Server Programming

A tiny attempt at making a dynamic update with loc

Author

Topic

colyte
Starting Member

3 Posts

Posted - 2010-05-21 : 11:49:22

Hello folks,

This is an attempt at making a slightly dynamic query to modify in bulk a CMDB at work.
Using SQL Server 2008.

I'm trying to update a column with a specific value.
I want the table, the column and the value to be inserted using local variables.
The intent is for myself, or someone else to just modify the variables.

My attempt, which is to follow does not work. But it should provide a fair enough logic behind what i want to do.

DECLARE @SYSTEMFELT as varchar(255),
      @SYSTEMVERDI as varchar(255),
      @CI_TABELL as varchar(255),
      @NYSYSTEMVERDI as varchar(255);
      
SET @CI_TABELL = 'CMDB2_CI_1'
SET @SYSTEMFELT = 'Systemansvarlig';
SET @NYSYSTEMVERDI = 'User1';
SET @SYSTEMVERDI = 'User2';

UPDATE @CI_TABELL
SET @SYSTEMFELT = @NYSYSTEMVERDI
WHERE @SYSTEMFELT = @SYSTEMVERDI

1. I'm not allowed to use a local variable to specify the table.
2. I'm not allowed to use a local variable for 'WHERE XXX = @LocalVariable'

Why is this so? And if someone knows how to work around it, I'd be very grateful!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 12:39:22

You need to use dynamic SQL for this. Here's an example:

DECLARE @sql varchar(500), @table varchar(100)

SET @table = 'Table1'

SET @sql = 'SELECT * FROM ' + @table

EXEC(@sql)

Dynamic SQL is bad for security and performance reasons. If your intent is just for someone to modify the variables, why can't you instead use sqlcmd mode in SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

colyte
Starting Member

3 Posts

Posted - 2010-05-21 : 13:24:36

tkizer,

I tried your way, ended up like the following:

begin tran
DECLARE @sql varchar(500),
		@SYSTEMFELT as varchar(255),
		@SYSTEMVERDI as varchar(255),
		@CI_TABELL as varchar(255),
		@NYSYSTEMVERDI as varchar(255);
		
SET @CI_TABELL = 'CMDB2_CI_1';
SET @SYSTEMFELT = 'Systemansvarlig';
SET @NYSYSTEMVERDI = 'User1';
SET @SYSTEMVERDI = 'User2';

SET @sql = 'update ' + @CI_TABELL +  ' SET ' + @SYSTEMFELT + ' = ' + @NYSYSTEMVERDI + ' WHERE ' + @SYSTEMFELT + ' = ' + @SYSTEMVERDI

EXEC(@sql)

Here's how the "select @sql" is:

update CMDB2_CI_1 SET Systemansvarlig = User1 WHERE Systemansvarlig = User2"

Unfortunately I get the error message:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'User2'.

Which doesn't make sense as the query is correct? Got any ideas?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 13:29:52

The value should be in single quotes..

Try this:

SET @SQL_STMT = 'UPDATE ' + @CI_TABELL +
' SET ' + @SYSTEMFELT + '=''' + @NYSYSTEMVERDI + '''
WHERE ' + @SYSTEMFELT + '=''' + @SYSTEMVERDI + ''''

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 13:30:22

The query isn't correct as you need single quotes are the varchar data values. Here you go:

SET @sql = 'update ' + @CI_TABELL + ' SET ' + @SYSTEMFELT + ' = ''' + @NYSYSTEMVERDI + ''' WHERE ' + @SYSTEMFELT + ' = ''' + @SYSTEMVERDI + ''''

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

colyte
Starting Member

3 Posts

Posted - 2010-05-21 : 18:38:06

Too tired now, but at first glance your final reply tkizer seems to be it.
In any case, the final working example is as follows:

DECLARE @sql nvarchar(max),
      @SYSTEMFELT as varchar(255),
      @SYSTEMVERDI as varchar(255),
      @CI_TABELL as varchar(255),
      @NYSYSTEMVERDI as varchar(255);
      
SET @CI_TABELL = 'CMDB2_CI_1';
SET @SYSTEMFELT = 'Systemansvarlig';
SET @NYSYSTEMVERDI = 'User1';
SET @SYSTEMVERDI = 'User2';

SET @sql = N'Update ' + @CI_TABELL + N' SET ' + @SYSTEMFELT + N' = ''' + @NYSYSTEMVERDI + N''' WHERE ' + @SYSTEMFELT + N' = ''' + @SYSTEMVERDI + N''''

It's just a query for internal use. A one shot type of thing.
In any case, just wanted to post the final working version for reference for anyone else.

Thanks for your time and effort!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources