动态SQL字符串查询引用表变量报错：必须声明标量变量@LocationIds

你遇到的错误核心原因是：动态SQL的执行上下文与外部独立，外部定义的表变量@LocationIds无法被动态SQL内部识别，且直接用'+ @LocationIds +'拼接字符串的方式本身不合法——表变量不能直接转换为字符串进行拼接。

下面提供三种可行的解决方案：

方案一：改用临时表 #

临时表在当前会话中全局可见，动态SQL可以直接访问，实现最简单：

DECLARE @query AS NVARCHAR(MAX), @YEAR AS NVARCHAR(4)
SET @YEAR = '2023'

-- 改用临时表替代表变量
CREATE TABLE #LocationIds(Id int)

INSERT INTO #LocationIds
VALUES (1), (13)

SET @query = N'SELECT LocationId, ['+@YEAR+'] FROM 
             (
                SELECT LocationId, IntegralApparentDelta, Year
                FROM vvMonitorWithDelta l 
                WHERE l.LocationId IN (SELECT Id FROM  #LocationIds)  
            ) x
            pivot (
                sum(IntegralApparentDelta)
                for Year in (['+ @YEAR +'])
            ) p'

PRINT @query
EXECUTE (@query)

-- 执行完毕后清理临时表
DROP TABLE #LocationIds

方案二：将表变量ID转为逗号分隔字符串拼接 #

把表变量中的ID提取为逗号分隔的字符串，直接拼入IN子句，适合ID数量较少的场景：

DECLARE @query AS NVARCHAR(MAX), @YEAR AS NVARCHAR(4)
SET @YEAR = '2023'

DECLARE @LocationIds AS TABLE(Id int)
INSERT INTO @LocationIds VALUES (1), (13)

-- 将表变量中的ID转换为逗号分隔的字符串
DECLARE @LocationStr AS NVARCHAR(MAX)
SELECT @LocationStr = STRING_AGG(Id, ',') FROM @LocationIds

SET @query = N'SELECT LocationId, ['+@YEAR+'] FROM 
             (
                SELECT LocationId, IntegralApparentDelta, Year
                FROM vvMonitorWithDelta l 
                WHERE l.LocationId IN ('+ @LocationStr +')  
            ) x
            pivot (
                sum(IntegralApparentDelta)
                for Year in (['+ @YEAR +'])
            ) p'

PRINT @query
EXECUTE (@query)

方案三：使用sp_executesql传递表参数 #

通过用户定义表类型，将表变量作为参数传递给动态SQL，安全性最高，适合复杂场景：

1. 先创建用户定义表类型（只需执行一次）：

CREATE TYPE LocationIdList AS TABLE (Id int)

2. 修改动态SQL代码：

DECLARE @query AS NVARCHAR(MAX), @YEAR AS NVARCHAR(4)
SET @YEAR = '2023'

DECLARE @LocationIds AS LocationIdList
INSERT INTO @LocationIds VALUES (1), (13)

SET @query = N'SELECT LocationId, ['+@YEAR+'] FROM 
             (
                SELECT LocationId, IntegralApparentDelta, Year
                FROM vvMonitorWithDelta l 
                WHERE l.LocationId IN (SELECT Id FROM @LocationIds)  
            ) x
            pivot (
                sum(IntegralApparentDelta)
                for Year in (['+ @YEAR +'])
            ) p'

PRINT @query
-- 使用sp_executesql传递表参数
EXECUTE sp_executesql @query, N'@LocationIds LocationIdList READONLY', @LocationIds = @LocationIds

内容的提问来源于stack exchange，提问作者Christian Alperto