Dynamic Column Name in SQL in Update statement

Question

DECLARE @sql NVARCHAR(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @sql = 'UPDATE [Table1] SET [Table1].[@columnName] = TEST';
SET @ParmDefinition = N'@columnName NVARCHAR(50)';
EXEC sp_executesql @sql, @ParmDefinition, @columnName = N'MyColumn';

When I run the above query, I get Invalid column name '@columnName'.. Clearly, the column name is not being replaced when the query is run.

In reality, my @sql variable is much larger and I have many columns I wish to update, thus I would like to avoid doing SET SQL = for all enumerations of the column name.

I'd like to declare the sql string once, and invoke the query with different values. e.g.:

EXEC sp_executesql @sql, @ParmDefinition, @columnName = N'MyColumn';
EXEC sp_executesql @sql, @ParmDefinition, @columnName = N'AnotherColumn';
EXEC sp_executesql @sql, @ParmDefinition, @columnName = N'YetAnotherColumn';
-- And so on

Is something like this possible?

Tab Alleman · Accepted Answer · 2015-01-08T23:04:31.757

5

Yes, you have to concatenate the variable outside the string. In other words:

SET @sql = 'UPDATE [Table1] SET [Table1].[' + @columnName + '] = t1.Value ' +

EDIT: Another solution we have used is to replace tokens in the base sql to construct a new sql variable for execution.

DECLARE @sql nvarchar(max) = 'SELECT @ColumnName FROM @TableName';

DECLARE @sql2 nvarchar(max) = REPLACE(REPLACE(@sql,'@ColumnName',@ColumnNameVariable),'@TableName',@TableNameVariable)

EXEC (@sql2)

...Some code that changes the values of @ColumnNameVariable and @TableNameVariable...

DECLARE @sql2 nvarchar(max) = REPLACE(REPLACE(@sql,'@ColumnName',@ColumnNameVariable),'@TableName',@TableNameVariable)

EXEC (@sql2)

And you'll notice that the Declaration and Exec of SQL2 are exactly the same lines in both cases. This lends itself to use in a LOOP if that is applicable. (Except that you wouldn't DECLARE @Sql2 in the loop...just populate/re-populate it).

edited Jan 08 '15 at 23:04

answered Jan 08 '15 at 22:53

Tab Alleman

31,483
7
36
52

I want to be able to run `sp_executesql ` and feed `@columnName` with different values though. – Chris Dargis Jan 08 '15 at 22:53
In reality, my @sql variable is much larger. I'd like to declare the sql string once, and invoke the query with different values. – Chris Dargis Jan 08 '15 at 22:55
Ok, I see what you mean. You can try Xedni's solution, or I will edit my answer with another solution we have used in the past. – Tab Alleman Jan 08 '15 at 22:57

score 3 · Answer 2 · answered Jan 08 '15 at 22:54

3

First of all, kudos for trying to parameterize your dsql using sp_executesql. The problem is, you can only parameterize something you could put into a variable in the first place such as in a search predicate or select list.

However it's still possible; just concatenate the column name with your DSQL string, wrapping it with the quotename function

set @sql = 'update table1 set table1.' + quotename(@ColumnName) + ' = ...

answered Jan 08 '15 at 22:54

Xedni

3,662
2
16
27

If I have (I do) multiple columns, I'd have to `SET @sql = ...` for each column, yes? – Chris Dargis Jan 08 '15 at 22:58
Yes, conditionally. I don't know exactly what you're trying to accomplish, but you could also theoretically construct a single string containing all the columns you want to update, and run it at once. Also, whether you update a single value in a row, or every column in the row, SQL has to do just as much work. Unless the columns in the are unknown at the time of performing the update, you could probably do this with statically typed SQL, and isnulls update table1 set Column1 = isnull(@column1, Column1) Column2, isnull(@column2, Column2) – Xedni Jan 08 '15 at 23:07

Dynamic Column Name in SQL in Update statement

2 Answers2