在SQL Server中映射动态列名称所在的动态变量整数

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

正如此链接中提到的在sql server中映射动态列名

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

我想生成列名，如col_1，col_2 ....参数@num是整数类型。

I have query as follows:

我的查询如下：

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

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

1 个解决方案

#1

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

此更新语句无法正常工作。您不能在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:

会发生什么是Sql Server威胁它作为两个不同的声明：

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.

由于update语句不包含SET子句，因此它基本上是一个无操作。

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

为了能够参数化标识符，您必须使用动态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

在rextester上亲自看看

#1