Dynamic column name in SQL in update statement
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 request, I get Invalid column name '@columnName'.
. Obviously, the column name is not replaced when the query is run.
Actually my @sql variable is much larger and I have a lot of columns that I want to update, so I would like to avoid SET SQL =
column name for all enums.
I would like to declare the sql string once and call the query with different values. eg:.
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 this possible?
source to share
Yes, you have to concatenate the variable outside of the string. In other words:
SET @sql = 'UPDATE [Table1] SET [Table1].[' + @columnName + '] = t1.Value ' +
EDIT: Another solution we used was to replace the tokens in the base sql in order to build a new sql variable to execute.
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 will notice that the declaration and Exec SQL2 in both cases are exactly the same strings. This allows for use in LOOP if applicable. (Except that you shouldn't DECLARE @ Sql2 in a loop ... just fill / refill it).
source to share
First of all, you should try to parameterize your dsql using sp_executesql. The problem is that you can only parameterize what you could put in a variable in the first place, like a search predicate or select a list.
However, this is still possible; just concatenate the column name with your DSQL string by wrapping it in a quotename function
set @sql = 'update table1 set table1.' + quotename(@ColumnName) + ' = ...
source to share