SQL循环执行动态查询报错：必须声明标量变量@cnt，求修复

这个错误的核心原因是动态SQL的执行上下文独立于外部批处理：你在外部声明的@cnt变量，在EXEC(@sql)执行动态SQL时，属于另一个完全独立的执行环境，内部根本识别不到外部的@cnt是什么。你写的动态SQL里有SELECT @cnt as 'Year'，这里的@cnt并没有被替换成实际年份数值，反而被当成动态SQL内部的变量，但你没在动态SQL里定义它，自然就触发了「必须声明标量变量」的错误。

我们需要把外部的@cnt变量值整合到动态SQL字符串中，而不是让动态SQL直接引用外部变量。下面提供几种可行的修复方式：

方式一：直接字符串拼接替换 #

最直接的方式是把@cnt转换成字符串后，直接拼接到动态SQL的SELECT语句里，让动态SQL执行时直接使用具体的年份数值：

DECLARE @cnt INT = 1959; 
WHILE @cnt < 2017 
BEGIN 
    DECLARE @sql NVARCHAR(1000) 
    SET @sql = 'SELECT ''' + CAST(@cnt AS VARCHAR(4)) + ''' as ''Year'', DC.[Code], PT.[Country Name], PT.[@colname] as ''totalGDP'', PG.[@colname] as ''growthGDP'' FROM [dbo].[GDP] PT INNER JOIN [dbo].[GDPgrowth] PG ON PT.[Country Name]=PG.[Country Name] INNER JOIN [dbo].[Dictionary] DC ON PT.[Country Name]=DC.[Country]' 
    SET @sql = REPLACE(@sql, '@colname', CAST(@cnt AS VARCHAR(255))); 
    EXEC(@sql); 
    SET @cnt = @cnt + 1; 
END;

方式二：用QUOTENAME规范列名拼接 #

如果想避免潜在的语法问题（比如列名含特殊字符，虽然年份不会出现，但养成好习惯），可以用QUOTENAME函数处理列名，让代码更健壮：

DECLARE @cnt INT = 1959; 
WHILE @cnt < 2017 
BEGIN 
    DECLARE @sql NVARCHAR(1000) 
    DECLARE @yearStr VARCHAR(4) = CAST(@cnt AS VARCHAR(4));
    DECLARE @colNameStr VARCHAR(4) = CAST(@cnt AS VARCHAR(4));
    SET @sql = N'SELECT ' + @yearStr + N' as [Year], DC.[Code], PT.[Country Name], PT.' + QUOTENAME(@colNameStr) + N' as [totalGDP], PG.' + QUOTENAME(@colNameStr) + N' as [growthGDP] 
                FROM [dbo].[GDP] PT 
                INNER JOIN [dbo].[GDPgrowth] PG ON PT.[Country Name]=PG.[Country Name] 
                INNER JOIN [dbo].[Dictionary] DC ON PT.[Country Name]=DC.[Country]';
    EXEC(@sql); 
    SET @cnt = @cnt + 1; 
END;

方式三：用sp_executesql传递参数（更安全） #

如果是复杂场景或涉及用户输入，推荐用sp_executesql传递参数，既能解决变量识别问题，还能防止SQL注入：

DECLARE @cnt INT = 1959; 
WHILE @cnt < 2017 
BEGIN 
    DECLARE @sql NVARCHAR(1000) 
    SET @sql = N'SELECT @Year as [Year], DC.[Code], PT.[Country Name], PT.' + QUOTENAME(CAST(@cnt AS VARCHAR(4))) + N' as [totalGDP], PG.' + QUOTENAME(CAST(@cnt AS VARCHAR(4))) + N' as [growthGDP] 
                FROM [dbo].[GDP] PT 
                INNER JOIN [dbo].[GDPgrowth] PG ON PT.[Country Name]=PG.[Country Name] 
                INNER JOIN [dbo].[Dictionary] DC ON PT.[Country Name]=DC.[Country]';
    EXEC sp_executesql @sql, N'@Year INT', @Year = @cnt;
    SET @cnt = @cnt + 1; 
END;

这种方式通过sp_executesql把外部的@cnt作为参数传递给动态SQL，内部的@Year就能正常识别外部变量的值了。

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