Search code examples
sqlsql-serverdynamic-columns

Mapping dynamic column names in SQL Server where dynamic variable integer

As mentioned in this link Mapping dynamic column names in sql server

I want to generate column names like col_1,col_2... .The parameter @num is of integer type.

I have query as follows:

DECLARE @num int = 15;
DECLARE @COL VARCHAR(50) = col_@step;

UPDATE table_tblName 
SET @COL = <some value> 
WHERE <condition>
Solution

  • This update statement can't work as it is. You can't parameterized identifiers in Sql.

    When you write something like this:

    UPDATE table_tblName SET @COL=<some value> WHERE <condition>,

    What happens is that Sql Server threat it as two different statements:

    UPDATE table_tblName

    And 

    SET @COL=<some value> WHERE <condition>

    Since the update statement does not contain the SET clause, it's basically a no-op.

    To be able to parameterize identifiers you must use dynamic sql - 

    DECLARE @num int = 15;
DECLARE @COL VARCHAR(50) = col_ + @step;
DECLARE @Sql VARCHAR(200) = 'UPDATE table_tblName SET ' + @COL + '=<some value> WHERE <condition>'
EXEC(@Sql)

    See for yourself on rextester