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?

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