循环执行存储过程遇SQL错误，求合并多物料清单结果方案

我执行以下SQL代码时遇到错误，想解决问题并合并多个DocumentID的物料清单结果：

DECLARE @files TABLE (DocumentID Varchar(50))

INSERT INTO @files (DocumentID)
SELECT DISTINCT D.DocumentID
FROM Documents D
WHERE D.DocumentID IN ('637542', '655437', '655900') --调试用示例

-- 循环处理
DECLARE @i INT = 1
DECLARE @rowCount INT = (SELECT COUNT(*) FROM @files)

WHILE @i <= @rowCount
BEGIN
   DECLARE @id Varchar(50) = (SELECT DocumentID FROM @files WHERE ROW_NUMBER() = @i)
   EXEC dbo.bom @docuid=@id
   SET @i = @i + 1
END

报错信息 #

Msg 10753, Level 15, State 3, Line 19
The function 'ROW_NUMBER' must have an OVER clause.
Msg 137, Level 15, State 2, Line 20
Must declare the scalar variable "@id".

dbo.bom是生成指定docuid物料清单的存储过程，单次执行结果如下：

我需要合并多个DocumentID的结果，预期输出：

想请教两个问题：

1. 在使用@i的情况下，怎么给ROW_NUMBER()加OVER子句？
2. 标量变量的错误提示具体是什么意思？

1. 标量变量错误的原因 #

第二个错误是因为@id的声明写在了WHILE循环内部。SQL在编译整个批处理时会检查变量声明，循环内的变量声明无法被后续的EXEC dbo.bom @docuid=@id识别，导致系统认为@id未声明。解决办法很简单，把@id的声明移到循环外部即可。

2. ROW_NUMBER()的OVER子句写法 #

ROW_NUMBER()必须搭配OVER()子句定义排序规则，否则无法生成有效的行号。要在循环中按行号取DocumentID，有两种常见写法：

写法一：给临时表加自增序号列 #

DECLARE @files TABLE (DocumentID Varchar(50))

INSERT INTO @files (DocumentID)
SELECT DISTINCT D.DocumentID
FROM Documents D
WHERE D.DocumentID IN ('637542', '655437', '655900')

-- 新建带自增行号的临时表
DECLARE @numberedFiles TABLE (RowNum INT IDENTITY(1,1), DocumentID Varchar(50))
INSERT INTO @numberedFiles(DocumentID)
SELECT DocumentID FROM @files

-- 循环处理
DECLARE @i INT = 1
DECLARE @rowCount INT = (SELECT COUNT(*) FROM @numberedFiles)
DECLARE @id Varchar(50) -- 移到循环外声明

WHILE @i <= @rowCount
BEGIN
   SELECT @id = DocumentID FROM @numberedFiles WHERE RowNum = @i
   EXEC dbo.bom @docuid=@id
   SET @i = @i + 1
END

写法二：用子查询生成行号 #

直接在查询里嵌套带ROW_NUMBER()的子查询，OVER子句里指定排序字段（比如按DocumentID排序）：

-- 替换循环内的SELECT语句
SELECT @id = DocumentID 
FROM (
    SELECT DocumentID, ROW_NUMBER() OVER(ORDER BY DocumentID) AS RowNum
    FROM @files
) t
WHERE t.RowNum = @i

3. 更高效的合并结果方式 #

用循环逐个执行存储过程，每次只能返回单独的结果集，没法直接合并。推荐两种更优的方式：

方式一：用临时表收集所有结果 #

-- 创建临时表，字段要和dbo.bom返回的结果一致
CREATE TABLE #AllBOMs (
    docuid Varchar(50),
    description Varchar(255),
    etc Varchar(255)
)

DECLARE @files TABLE (DocumentID Varchar(50))
INSERT INTO @files (DocumentID)
SELECT DISTINCT D.DocumentID
FROM Documents D
WHERE D.DocumentID IN ('637542', '655437', '655900')

DECLARE @id Varchar(50)
-- 用游标遍历DocumentID
DECLARE docCursor CURSOR FOR SELECT DocumentID FROM @files
OPEN docCursor
FETCH NEXT FROM docCursor INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 将存储过程结果插入临时表
    INSERT INTO #AllBOMs
    EXEC dbo.bom @docuid=@id
    
    FETCH NEXT FROM docCursor INTO @id
END

CLOSE docCursor
DEALLOCATE docCursor

-- 查询合并后的最终结果
SELECT * FROM #AllBOMs
DROP TABLE #AllBOMs

方式二：将存储过程改为表值函数（推荐） #

如果能修改原存储过程，建议把dbo.bom改成表值函数，这样可以直接用SELECT语句批量获取结果，性能和代码简洁度都远高于循环：

-- 假设表值函数的定义（根据原存储过程逻辑改写）
CREATE FUNCTION dbo.fn_bom(@docuid Varchar(50))
RETURNS TABLE
AS
RETURN (
    -- 这里写原dbo.bom中的查询逻辑
    SELECT docuid, description, etc FROM [你的表名] WHERE docuid = @docuid
)

-- 批量查询并合并结果
SELECT b.*
FROM @files f
CROSS APPLY dbo.fn_bom(f.DocumentID) b

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