由多个查询影响的行的返回编号

Question

我正在在SQL Server中编写一些动态SQL。有一些查询合并为一个。第一个查询选择数据，第二个删除，第三个插入物，最后删除了一个临时表：

SET @completeSQL = @tempProdSQL + @deleteSQL + @insertSQL + ' DROP TABLE #tempProd'
PRINT(@completeSQL)
EXEC sp_ExecuteSQL @completeSQL
SET @archiveSize = @@ROWCOUNT

我知道我可以使用@@Rowcount，但是我得到的值为0。我相信这是因为最后一个命令是丢弃表。

有没有办法在@tempProdSQL命令之后将行受到影响？如果执行会影响性能，我希望不将执行分开。

Answer 1

说明：

在sp_executesql的文档中，有一个"备注"部分说：

sp_executesql具有与批处理相同的行为， 名称的范围和数据库上下文。Transact-SQL语句 或在SP_EXECUTESQL中的批量STMT参数直到 执行SP_EXECUTESQL语句。然后是STMT的内容 与执行分开编译并执行为执行计划 称为sp_executesql的批次计划。SP_EXECUTESQL批处理 无法在调用批处理中声明的变量 sp_executesql。SP_EXECUTESQL批处理中的本地光标或变量 调用sp_executesql的批处理不可见。改变在 数据库上下文仅持续到sp_executesql语句的末尾。

另一种说法的方式是，因为直到运行时才汇编动态SQL，它被视为单独的批次，并且在过程构建/执行过程之外有自己的上下文。

如果您将其他参数（为 sp_executesql）（为 @params and @param1），则可以在动态SQL语句中找到@@row_count，然后将该值传递回主批次/上下文。

。

解决方案：

编辑：在动态SQL的@tempProdSQL之后立即移动@@rowcount检查。

declare @row_cnt int --add this to your procedure's variables
set @completeSQL = @tempProdSQL + ' set @row_cnt_dyn = @@rowcount ' + @deleteSQL + @insertSQL + ' DROP TABLE #tempProd' 
print (@completeSQL)
exec sp_executesql @completeSQL
        , N'@row_cnt_dyn int out' --establishing the param in the dynamic sql batch
        , @row_cnt_dyn = @row_cnt out --this is getting the value out of the dynamic batch into the main batch
set @archiveSize = @row_cnt

Answer 2

您可以在@tempprodsql之后立即捕获@@ rowcount的值，然后使用输出参数将其返回到外面上下文：

SET @completeSQL = @tempProdSQL + '; SET @archiveSize1 = @@ROWCOUNT; ' + @deleteSQL + @insertSQL + ' DROP TABLE #tempProd'
PRINT(@completeSQL)
EXEC sp_executesql @completeSQL, N'@archiveSize1 INT OUTPUT', @archiveSize1 = @archiveSize OUTPUT
SELECT '@archiveSize:', @archiveSize