@@ ROWCOUNT将给出受最后一条SQL语句影响的行数,最好在有问题的命令后将其捕获到局部变量中,因为下次您查看它时,其值将更改:
DECLARE @Rows int
DECLARE @TestTable table (col1 int, col2 int)
INSERT INTO @TestTable (col1, col2) select 1,2 union select 3,4
SELECT @Rows=@@ROWCOUNT
SELECT @Rows AS Rows,@@ROWCOUNT AS [ROWCOUNT]
输出:
(2 row(s) affected)
Rows ROWCOUNT
----------- -----------
2 1
(1 row(s) affected)
您得到Rows的值为2,即插入的行数,但是ROWCOUNT为1,因为SELECT @Rows=@@ROWCOUNT命令影响了1行
如果您的事务中有多个INSERT或UPDATE等,则需要确定如何“计算”正在发生的事情。 您可以为每个表分别设置总计,单个总计值或完全不同的值。 您需要为每个要跟踪的总计选择一个变量,并在对其应用以下每个操作之后将其添加到该变量:
--note there is no error handling here, as this is a simple example
DECLARE @AppleTotal int
DECLARE @PeachTotal int
SELECT @AppleTotal=0,@PeachTotal=0
BEGIN TRANSACTION
INSERT INTO Apple (col1, col2) Select col1,col2 from xyz where ...
SET @AppleTotal=@AppleTotal+@@ROWCOUNT
INSERT INTO Apple (col1, col2) Select col1,col2 from abc where ...
SET @AppleTotal=@AppleTotal+@@ROWCOUNT
INSERT INTO Peach (col1, col2) Select col1,col2 from xyz where ...
SET @PeachTotal=@PeachTotal+@@ROWCOUNT
INSERT INTO Peach (col1, col2) Select col1,col2 from abc where ...
SET @PeachTotal=@PeachTotal+@@ROWCOUNT
COMMIT
SELECT @AppleTotal AS AppleTotal, @PeachTotal AS PeachTotal