WHILE语句无法识别已声明的@loop变量问题求助

错误原因 #

你编写的动态SQL运行在独立的执行上下文里，外部声明的@loop变量无法被动态SQL内部识别，因此会抛出「必须声明标量变量@loop」的错误。

解决方案1：参数化动态SQL（推荐） #

使用sp_executesql执行动态SQL，并将@loop作为参数传入，这是更安全、规范的写法，还能避免SQL注入风险：

DECLARE @sql NVARCHAR(MAX)
DECLARE @loop INT = 1
DECLARE @max INT

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
BEGIN 
    DROP TABLE #temp 
END

SELECT
    0 AS row_counter, ID 
INTO
    #temp 
FROM
    Requests
WHERE 
    (Codes_IDs LIKE '%6581%' OR Codes_IDs LIKE '%6597%') 
    AND Codes_IDs LIKE '%6393%' 
    AND Type_ID = 6477 
    AND Codes_IDs NOT LIKE '%6484%'

DECLARE @counter int = 0

UPDATE #temp 
SET @counter = row_counter = @counter + 1
--select * from #temp

SET @max = (SELECT MAX(row_counter) FROM #temp)

WHILE (@loop <= @max)
BEGIN
    -- 定义带参数占位符的动态SQL
    SET @sql = N'Select * from requests where ID in (select ID from #temp where row_counter = @param_loop)'
    -- 通过sp_executesql传入参数
    EXEC sp_executesql @sql, N'@param_loop INT', @param_loop = @loop
    SET @loop = @loop +1
END

DROP TABLE #temp;

解决方案2：直接拼接变量到动态SQL（仅测试场景用） #

把@loop的值直接拼接到SQL字符串中，写法简单但存在SQL注入风险，适合临时测试：

DECLARE @sql VARCHAR(MAX)
DECLARE @loop INT = 1
DECLARE @max INT

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
BEGIN 
    DROP TABLE #temp 
END

SELECT
    0 AS row_counter, ID 
INTO
    #temp 
FROM
    Requests
WHERE 
    (Codes_IDs LIKE '%6581%' OR Codes_IDs LIKE '%6597%') 
    AND Codes_IDs LIKE '%6393%' 
    AND Type_ID = 6477 
    AND Codes_IDs NOT LIKE '%6484%'

DECLARE @counter int = 0

UPDATE #temp 
SET @counter = row_counter = @counter + 1
--select * from #temp

SET @max = (SELECT MAX(row_counter) FROM #temp)

WHILE (@loop <= @max)
BEGIN
    -- 将@loop的值转为字符串后拼接到SQL语句
    SET @sql = 'Select * from requests where ID in (select ID from #temp where row_counter = ' + CAST(@loop AS VARCHAR(10)) + ')'
    EXEC(@sql)
    SET @loop = @loop +1
END

DROP TABLE #temp;

补充提示 #

后续如果要在循环中添加UPDATE语句，同样推荐用参数化方式传递变量，避免重复出现类似错误。另外，会话级临时表#temp在动态SQL中可以正常识别，无需额外处理。

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