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?

Basidium

Answer 1

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).

Guarantee

I want to be able to run sp_executesql and feed @columnName with different values though. – Basidium

In reality, my @sql variable is much larger. I'd like to declare the sql string once, and invoke the query with different values. – Basidium

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. – Guarantee

Answer 2

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) + ' = ...

Sublimity

If I have (I do) multiple columns, I'd have to SET @sql = ... for each column, yes? – Basidium

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) – Sublimity