SQL Update columns passing into the query the column name and value

Question

Hi,

I have the following code:

UPDATE myTable
SET    Col1 = @Value

However, I have a table that has over a 100 columns and want to be able to specify a column name by passing the name into the query similar to:

UPDATE myTable
SET    @ColName = @Value

When I do this I get an error. Is there a good solution to this? Its probably something simple!

Thank you in advanced.

Answer 1

You'd have to revert to dynamic SQL to do this.

Answer 2

You'll have to use dynamic SQL, and write it to make sure you don't let Little Bobby Tables in. Something like this:

DECLARE @sql NVARCHAR(500)
SET @sql = N'UPDATE myTable SET ' + QUOTENAME(@colName) + ' = @pUpdateVal'
EXEC sp_ExecuteSQL @sql, '@pUpdateVal NVARCHAR(20)', @value

Make sure you change the type of @pUpdateVal to something appropriate for your environment, but this will mitigate the risk of injection attacks.

Answer 3

Agreed with the others, you'll need dynamic SQL for this; you can't define object names at run time in native SQL. For a full discussion on dynamic SQL see http://www.sommarskog.se/dynamic%5Fsql.html