SSMS动态SQL中PIVOT变量失效，禁用全局临时表的解决办法

你的代码核心问题是PIVOT子句的IN列表不支持直接使用变量，SQL Server不会将@YearValues解析为具体的列名列表。原代码中动态SQL内部生成@YearValues的写法，即便能获取到值，也无法被PIVOT识别。而全局临时表能“正常运行”大概率是巧合，本质上该写法仍不符合PIVOT的语法要求。

正确的做法是在动态SQL外部先生成年份列名列表，再将其直接拼入动态SQL字符串的PIVOT子句中，具体步骤如下：

步骤1：在外层生成年份列名列表 #

先从局部临时表#BaseTable中提取去重的年份，拼接成符合PIVOT要求的列名字符串（用QUOTENAME包裹避免语法错误）：

DECLARE @YearValues AS NVARCHAR(MAX);

SELECT @YearValues = COALESCE(@YearValues + ', ', '') + QUOTENAME(Y.Years)
FROM (
    SELECT DISTINCT BT.Years 
    FROM #BaseTable AS BT
) AS Y
ORDER BY Y.Years;

-- 处理无年份数据的边界情况
IF @YearValues IS NULL
BEGIN
    PRINT '无可用年份数据';
    RETURN;
END

步骤2：构建并执行正确的动态SQL #

将生成的@YearValues直接拼入PIVOT的IN子句中，同时注意在Source子查询中必须包含要聚合的ShareValue列（原代码遗漏了该列，会导致PIVOT报错）：

DECLARE @SQL AS NVARCHAR(MAX);

SET @SQL = N'
SELECT 
    PVT.Region,
    PVT.prdName,
    PVT.Type,
    PVT.Unit,
    PVT.ShareValue,
    ''' + @YearValues + ''' AS YearValues
FROM (
    SELECT 
        BT.Region,
        BT.prdName,
        BT.Unit,
        BT.Type,
        BT.Years,
        BT.ShareValue -- 必须包含该列，供PIVOT聚合使用
    FROM #BaseTable AS BT
    WHERE EXISTS (
        SELECT 1 
        FROM #TypeSelected AS TS 
        WHERE TS.Type = BT.Type
    )
) AS Source
PIVOT (
    MAX(ShareValue) FOR Years IN (' + @YearValues + ')
) AS PVT';

EXEC sys.sp_executesql @stmt = @SQL;

1. 局部临时表的作用域：局部临时表#BaseTable在同一存储过程的会话中，动态SQL可以正常访问，无需改为全局临时表。
2. 避免SQL注入：使用QUOTENAME包裹年份值，确保即使年份包含特殊字符（如空格、符号），也不会引发语法错误或SQL注入风险。
3. 边界处理：增加了无年份数据时的判断，避免因@YearValues为空导致动态SQL执行报错。

内容的提问来源于stack exchange，提问作者Nallagatla Hari