如何编写TSQL存储过程循环处理零件号并更新主表？

现有查询仅能处理单个零件号的更新需求，但OSR_MAIN表中存在多个零件号记录，需要批量执行该逻辑完成主表更新。计划通过存储过程实现，但不清楚如何在TSQL中使用WHILE循环遍历OSR_MAIN表的所有零件号记录。

单零件号处理代码 #

DECLARE @ID Int
DECLARE @PartNum VarChar(10)
DECLARE @TopJob Int

SET @PartNum = '12345678'
SET @ID = (SELECT TOP(1) CountQty FROM dbo.OSR_MAIN WHERE PartNumber=@PartNum)

SET @TopJob = (
SELECT Brdcst_Nbr
  FROM dbo.Available_PerPart
  WHERE PartNumber = @PartNum
  ORDER BY Brdcst_Nbr 
  OFFSET (@ID) ROWS FETCH NEXT 1 ROWS ONLY
  )

PRINT @ID
PRINT @PartNum
PRINT @TopJob
  
UPDATE dbo.OSR_MAIN SET dbo.OSR_MAIN.TopJob=@TopJob WHERE [PartNumber]=@PartNum;

参考示例场景 #

源数据 #

Main表

Orders表

目标输出 #

解决方案：使用WHILE循环的存储过程 #

实现代码 #

CREATE PROCEDURE dbo.UpdateOSRMainTopJob
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量
    DECLARE @PartNum VarChar(10), @ID Int, @TopJob Int;

    -- 创建临时表存储待处理零件号
    CREATE TABLE #TempParts (
        PartNumber VarChar(10) PRIMARY KEY,
        Processed BIT DEFAULT 0
    );

    -- 导入OSR_MAIN中所有零件号
    INSERT INTO #TempParts (PartNumber)
    SELECT DISTINCT PartNumber FROM dbo.OSR_MAIN;

    -- 循环处理每个零件号
    WHILE EXISTS (SELECT 1 FROM #TempParts WHERE Processed = 0)
    BEGIN
        -- 获取第一条未处理的零件号
        SELECT TOP 1 @PartNum = PartNumber FROM #TempParts WHERE Processed = 0;

        -- 获取当前零件号的CountQty值
        SET @ID = (SELECT TOP(1) CountQty FROM dbo.OSR_MAIN WHERE PartNumber = @PartNum);

        -- 获取偏移量对应的Brdcst_Nbr
        SET @TopJob = (
            SELECT Brdcst_Nbr
            FROM dbo.Available_PerPart
            WHERE PartNumber = @PartNum
            ORDER BY Brdcst_Nbr 
            OFFSET (@ID) ROWS FETCH NEXT 1 ROWS ONLY
        );

        -- 打印调试信息（可选）
        PRINT '处理零件号: ' + @PartNum;
        PRINT '偏移量ID: ' + CAST(@ID AS VARCHAR);
        PRINT 'TopJob值: ' + CAST(@TopJob AS VARCHAR);

        -- 更新主表
        UPDATE dbo.OSR_MAIN 
        SET TopJob = @TopJob 
        WHERE PartNumber = @PartNum;

        -- 标记当前零件号已处理
        UPDATE #TempParts 
        SET Processed = 1 
        WHERE PartNumber = @PartNum;
    END

    -- 清理临时表
    DROP TABLE #TempParts;
END
GO

调用存储过程 #

EXEC dbo.UpdateOSRMainTopJob;

更高效的替代方案：基于集合的更新 #

TSQL中基于集合的操作性能远优于循环，建议优先采用此方式：

WITH PartTopJob AS (
    SELECT 
        p.PartNumber,
        FIRST_VALUE(ap.Brdcst_Nbr) OVER (
            PARTITION BY p.PartNumber 
            ORDER BY ap.Brdcst_Nbr 
            OFFSET p.CountQty ROWS FETCH NEXT 1 ROWS ONLY
        ) AS TopJob
    FROM dbo.OSR_MAIN p
    JOIN dbo.Available_PerPart ap ON p.PartNumber = ap.PartNumber
)
UPDATE dbo.OSR_MAIN
SET TopJob = ptj.TopJob
FROM dbo.OSR_MAIN m
JOIN PartTopJob ptj ON m.PartNumber = ptj.PartNumber;

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