如何高效针对参考表每行执行指定存储过程？

一、最优方案：直接用批量UPDATE替代存储过程调用 #

如果你的存储过程核心逻辑仅为更新CompanyTable的CompanyID，完全不需要循环调用存储过程——直接通过JOIN参考表完成批量更新是性能最高的方式，集合操作是SQL的原生优势，远优于逐行执行：

UPDATE ct
SET ct.CompanyID = ref.NewCompanyID
FROM CompanyTable ct
JOIN 参考表 ref ON ct.CompanyID = ref.OldCompanyID
-- 如需过滤特定行，可添加WHERE条件
-- WHERE ref.OldCompanyID IN (...)

二、必须调用存储过程时的高效方案 #

若存储过程包含复杂逻辑（如更新关联表、记录操作日志等），无法直接用单条UPDATE替代，推荐以下两种方式：

1. 使用表值参数（Table-Valued Parameter, TVP） #

这是SQL Server中批量传递参数的高效方式，步骤如下：

第一步：创建表值类型

CREATE TYPE CompanyMoveType AS TABLE (
    OldCompanyID INT,
    NewCompanyID INT
);

第二步：修改存储过程接受表值参数

ALTER PROCEDURE YourProcedureName
    @CompanyMoves CompanyMoveType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 批量处理表值参数中的数据，保留原存储过程的其他逻辑
    UPDATE ct
    SET ct.CompanyID = cm.NewCompanyID
    FROM CompanyTable ct
    JOIN @CompanyMoves cm ON ct.CompanyID = cm.OldCompanyID;

    -- 在此添加原存储过程的其他逻辑（如日志记录、关联表更新等）
END

第三步：调用存储过程时传入参考表数据

DECLARE @Moves CompanyMoveType;
INSERT INTO @Moves (OldCompanyID, NewCompanyID)
SELECT OldCompanyID, NewCompanyID FROM 参考表;

EXEC YourProcedureName @CompanyMoves = @Moves;

2. 使用WHILE循环逐行处理（比游标高效） #

如果无法修改存储过程，只能逐行调用，WHILE循环的资源开销远低于游标：

-- 将参考表数据存入临时表并添加自增ID
SELECT ROW_NUMBER() OVER (ORDER BY OldCompanyID) AS RowNum,
       OldCompanyID, NewCompanyID
INTO #TempMoves
FROM 参考表;

DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #TempMoves);
DECLARE @CurrentRow INT = 1;
DECLARE @From INT, @To INT;

WHILE @CurrentRow <= @TotalRows
BEGIN
    SELECT @From = OldCompanyID, @To = NewCompanyID
    FROM #TempMoves
    WHERE RowNum = @CurrentRow;

    EXEC YourProcedureName @Company_Move_From = @From, @Company_Move_To = @To;

    SET @CurrentRow = @CurrentRow + 1;
END

DROP TABLE #TempMoves;

三、不推荐但可行的方式：动态SQL批量生成EXEC语句 #

适合小批量数据，生成多条EXEC语句一次性执行：

DECLARE @Sql NVARCHAR(MAX) = '';

SELECT @Sql = @Sql + 'EXEC YourProcedureName @Company_Move_From = ' + CAST(OldCompanyID AS NVARCHAR) + ', @Company_Move_To = ' + CAST(NewCompanyID AS NVARCHAR) + ';' + CHAR(13)
FROM 参考表;

EXEC sp_executesql @Sql;

注：若涉及字符串类型参数，需使用参数化动态SQL避免注入风险。

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