如何遍历CTE结果集？执行SQL遇Msg208错误求助

问题原因 #

CTE（公共表表达式）的作用域仅限于定义它之后的第一条SQL语句。你代码中，CTE list 定义后仅在紧接着的 SELECT @counter = min(id) , @MaxId = max(id) FROM list 语句中有效，后续WHILE循环里的SELECT @name = KodDokumentu FROM list已超出CTE的作用域，因此会报“无效的对象名'list'”错误。

解决方案 #

要在循环中反复访问CTE的结果，需先将CTE的数据持久化到临时表或表变量中，之后再操作这个持久化对象。

修改后的代码（使用表变量）

DECLARE @end datetime = getdate(),
    @start datetime = CAST('01/01/2023' as datetime),
    @counter INT,
    @MaxId INT, 
    @name NVARCHAR(100);

-- 定义表变量存储CTE结果
DECLARE @list TABLE (id INT, KodDokumentu NVARCHAR(100))

WITH list as (
    SELECT  DISTINCT ROW_NUMBER() OVER( ORDER BY KodDokumentu) as id, KodDokumentu
    FROM [dbo].[view_list]
    WHERE IdMagazynu = 15 and IdStatusu >= 50 and DataZatw >= @start and DataZatw <= @end
    GROUP BY KodDokumentu
)
-- 将CTE数据插入表变量
INSERT INTO @list(id, KodDokumentu)
SELECT id, KodDokumentu FROM list

-- 初始化循环变量
SELECT @counter = min(id) , @MaxId = max(id) 
FROM @list

WHILE(@counter IS NOT NULL AND @counter <= @MaxId)
BEGIN
    SELECT @name = KodDokumentu
    FROM @list WHERE id = @counter

    PRINT CONVERT(VARCHAR,@counter) + '. kod dokumentu ' + @name 
    SET @counter  = @counter  + 1        
END

可选优化：用集合操作替代循环（更符合SQL设计思想）

如果业务逻辑允许，建议避免WHILE循环（SQL对集合操作的效率远高于逐行循环），直接输出结果：

DECLARE @end datetime = getdate(),
    @start datetime = CAST('01/01/2023' as datetime);

WITH list as (
    SELECT  DISTINCT ROW_NUMBER() OVER( ORDER BY KodDokumentu) as id, KodDokumentu
    FROM [dbo].[view_list]
    WHERE IdMagazynu = 15 and IdStatusu >= 50 and DataZatw >= @start and DataZatw <= @end
    GROUP BY KodDokumentu
)
SELECT CONVERT(VARCHAR,id) + '. kod dokumentu ' + KodDokumentu
FROM list
ORDER BY id

内容的提问来源于stack exchange，提问作者Michał Gałecki