Dynamic column update - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Dynamic column update

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-01-20 : 04:36:57

Hi, I want to do something like this, how can I do this:

@ID int,
@ColumnName nvarchar(255),
@ColumnValue nvarchar(255)

as

UPDATE
[tablename]
SET
@ColumnName = @ColumnValue
WHERE
[ID ] = @ID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-20 : 04:40:07

only with Dynamic SQL.

read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-01-20 : 04:43:05

DECLARE @ID int,
@ColumnName nvarchar(255),
@ColumnValue nvarchar(255)

DECLARE @QueryStr NVARCHAR(500)
SELECT @QueryStr = '
UPDATE
[tablename]
SET '+
@ColumnName+ ' = '+ @ColumnValue+ '
WHERE
[ID ] = '+@ID

EXEC (@QueryStr)

--------------------------
http://connectsql.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-01-20 : 06:49:07

Actually I am doing:

@quizID int,
@quizCookie nvarchar(255),
@quizWhichQuestion nvarchar(255),
@quizAnswer nvarchar(255)

AS

DECLARE @QueryStr NVARCHAR(500)

SELECT @QueryStr = '
UPDATE
[quiz]
SET
'+@quizWhichQuestion+' = '+@quizAnswer+'
WHERE
[Cookie] = '+@quizCookie+'
AND
[quizID ] = '+@quizID

EXEC (@QueryStr)

But I get a Conversion failed when converting the nvarchar value '....' to data type int.

The secret to creativity is knowing how to hide your sources. (Einstein)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 06:59:26

SELECT @QueryStr = '
UPDATE
[quiz]
SET
'+@quizWhichQuestion+' = '''+@quizAnswer+'''
WHERE
[Cookie] = '''+@quizCookie+'''
AND
[quizID ] = '+ convetr(varchar(20),@quizID)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources