返回在使用IF语句时更改了多少行

Question

我正在SQL中运行一些更新语句，我想向Audit表返回受影响的行数。现在我明白了(并看到了一些例子(，@ROWCOUNT可以用来做这件事。问题是我使用的是一个带有IF语句的update语句。

BEGIN TRANSACTION
WHILE 1=1
BEGIN
BEGIN TRANSACTION 
--update a 1000 rows at a time
UPDATE TOP (1000) table1
SET flag = 1,
WHERE ID IN (SELECT ID FROM #list)      
IF @@ROWCOUNT = 0 -- terminating condition
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
WAITFOR DELAY '00:00:01';
END
--COMMIT TRANSACTION
ROLLBACK TRANSACTION
GO

现在我想使用以下内容。

INSERT INTO @Audit
SELECT 'table1', @@ROWCOUNT

但无论我把它放在更新的哪里，它总是返回0。我试着把它重新分配给另一个变量，但仍然没有什么乐趣。

任何关于这方面的建议都会很棒。

如果需要更多信息，请询问：(

Answer 1

使用临时变量存储@@ROWCOUNT值。在IF线路中进行测试后，该值被设置为0。

DECLARE @MY_ROW_CNT AS INT;   
BEGIN
BEGIN TRANSACTION 
--update a 1000 rows at a time
UPDATE TOP (1000) table1
SET flag = 1,
WHERE ID IN (SELECT ID FROM #list) 
SET @MY_ROW_CNT = @@ROWCOUNT;

IF @MY_ROW_CNT = 0 -- terminating condition
BEGIN
COMMIT TRANSACTION
BREAK
END
INSERT INTO @Audit
SELECT 'table1', @MY_ROW_CNT;
COMMIT TRANSACTION
WAITFOR DELAY '00:00:01';
END

Answer 2

如果您在外部声明一个值，它可以工作，请确保您的变量范围。

CREATE TABLE T(i int);
insert T values (1);
insert T values (1);
insert T values (1);
DECLARE @CountNum int -- if it set outside is ok
BEGIN
BEGIN TRANSACTION 
UPDATE TOP (1000) T
SET i = 111
SELECT @CountNum = @@ROWCOUNT
IF @@ROWCOUNT = 0 -- terminating condition
BEGIN
COMMIT TRANSACTION
END
COMMIT TRANSACTION
WAITFOR DELAY '00:00:01';
END

SELECT @CountNum

sqlfiddle

Answer 3

您可以尝试下面的查询，在审计表中插入受影响的日期时间记录，如下所示。

CREATE TABLE #AuditTable (dtDateTime datetime default getdate(), NoOfRecords int)--Creating Audit Table
CREATE TABLE #Temp (sEmployeeName Varchar(50))
INSERT INTO #Temp VALUES ('A')
INSERT INTO #Temp VALUES ('B')
INSERT INTO #Temp VALUES ('C')
INSERT INTO #Temp VALUES ('D')
INSERT INTO #Temp VALUES ('E')
INSERT INTO #Temp VALUES ('F')
--SELECT * FROM #Temp --Before Update
UPDATE #Temp SET sEmployeeName = 'U - ' + sEmployeeName --Update
INSERT INTO #AuditTable (NoOfRecords) SELECT @@RowCount--No. of Records Updated
SELECT * FROM #AuditTable --Audit Table
DROP TABLE #Temp
DROP TABLE #AuditTable

输出如下

dtDateTime                 NoOfRecords
2018-10-29 18:05:11.280    6

希望这对你有帮助。

Answer 4

我放弃了第一个事务并重新安排了一些部分。我在脚本的开头声明了两个变量。您似乎已经有了@audit表变量，所以您可能需要省略该部分。这个脚本最重要的部分是，它不会进入无休止的循环，只更新没有设置为1:标志的行

DECLARE @audit table(col1 varchar(20), rows int)
DECLARE @counter INT = 1000
WHILE @counter= 1000
BEGIN
BEGIN TRANSACTION 
--update a 1000 rows at a time
;WITH CTE as
(
SELECT distinct id
FROM #list
)
UPDATE TOP (1000) t1
SET flag = 1
FROM table1 t1
JOIN #list lst
ON t1.ID = lst.id
and (t1.flag <> 1 or t1.flag is null)
SELECT @counter = @@ROWCOUNT
INSERT INTO @Audit values('table1', @counter)
COMMIT TRANSACTION
WAITFOR DELAY '00:00:01';
END

Answer 5

我不会使用while 1=1，而是使用一个更符合逻辑的循环，就像这样
这永远不应该成为一个无限循环，它应该在审核表中正确写入，而不需要break构造。

declare @Audit table(tableName varchar(20), RowsUpdated int)
declare @RowsUpdated int = -1
WHILE @RowsUpdated <> 0
BEGIN
BEGIN TRANSACTION 
--update a 1000 rows at a time
UPDATE TOP (1000) table1
SET    flag = 1
WHERE  ID IN (SELECT ID FROM #list)    
AND    (flag <> 1 or flag is null)
set @RowsUpdated = @@ROWCOUNT
INSERT INTO @Audit
SELECT 'table1', @RowsUpdated;
COMMIT TRANSACTION
WAITFOR DELAY '00:00:01';
END
GO