使用sp_executesql执行动态SQL无法获取@cursRows返回值如何解决

问题根因 #

1. sp_executesql参数映射逻辑错误
   你当前的写法没有建立动态SQL内部和外部变量的输出绑定：

• 动态SQL内部声明的@cursRows和sp_executesql定义的@cursRowsOUT没有关联，值无法传递到外部
• 参数赋值方向搞反，且未标记OUTPUT关键字，输出值无法返回到外层作用域

2. 更新表语句语法错误
   你写的SET [dbo].[BatchTable]是错误语法，正确更新语句需要用UPDATE关键字开头。

修复后的代码 #

DECLARE 
        @batchRow int, -- 批次包含的第一行行号
        @batchSize int, -- 计算得到的批次大小
        @batchProcess nvarchar(100), -- 批次处理的存储过程名
        @batchSql nvarchar(max), -- 批次执行语句
        @cursRows int; -- 存储过程游标加载的行数

SELECT @batchSize = 10, @batchRow = 0, @batchProcess = 'SYNCHRONIZE_ALL_CARS';

-- 动态SQL直接使用传入的输出参数，不需要内部额外声明变量
SELECT @batchSQL = concat(N'EXEC [dbo].[', @batchProcess, N'] @batchRow= ', @batchRow, N', @batchSize= ', @batchSize, N', @cursRows = @InnerCursRows OUTPUT');

-- 建立内外参数映射，标记OUTPUT才能拿到返回值
EXEC sp_executesql @batchSQL, N'@InnerCursRows int OUTPUT', @InnerCursRows = @cursRows OUTPUT;

-- 正确的更新表语句，按需补充WHERE条件定位到对应批次记录
UPDATE [dbo].[BatchTable] SET BatchSize = @cursRows;

优化建议（可选） #

如果担心拼接动态SQL时的注入风险，也可以把@batchRow和@batchSize也作为参数通过sp_executesql传递，不需要直接拼接到字符串里，写法更安全：

SELECT @batchSQL = N'EXEC [dbo].' + QUOTENAME(@batchProcess) + N' @batchRow= @pBatchRow, @batchSize= @pBatchSize, @cursRows = @InnerCursRows OUTPUT';
EXEC sp_executesql @batchSQL, 
    N'@pBatchRow int, @pBatchSize int, @InnerCursRows int OUTPUT',
    @pBatchRow = @batchRow,
    @pBatchSize = @batchSize,
    @InnerCursRows = @cursRows OUTPUT;

内容的提问来源于stack exchange，提问作者Márton Kovács