Returning how many rows changed while IF statement is used?

Question

I am running some update statements in SQL and I want to return to an Audit table how many rows have been affected. Now I understand (and have seen examples) that a @@ROWCOUNT can be used to do this. The problem is I'm using an update statement with an IF statement.

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

Now I want to use the something like the following.

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

But no matter where I put it in the update it always returns 0. I have tried reassigning it to a different variable but still no joy.

Any pointers on this would be great.

If more information is needed please ask :)

your IF statement has nothing to do with your update. Also, why do a commit when @@ROWCOUNT = 0 ? Nothing has updated in that case, so why commit ? — GuidoG, Oct 29 '18 at 12:03
It seems to me like there just where no updates. What do you get when you do a `select` in stead of `update` ? — GuidoG, Oct 29 '18 at 12:06
My understanding is that the update statement does a 1000 rows at a time. If there are more rows to update it continues. If none (ROWCOUNT = 0) then it breaks out and finishes. Updates I have done in the past have worked using the above formula. Unless I've just been getting lucky. — JimmyPop13, Oct 29 '18 at 12:10
Unless I am mistaken this can update the first 1000 rows (random since you have no order by) but will stop there. If there are more than 1000 rows coming out of your where clause, only the first 1000 will be updated, not the others. Am I correct in this thinking or not ? So unless this in inside a loop, it will only update 1000 max, right ? — GuidoG, Oct 29 '18 at 12:36
I do use While 1 = 1 at the start. I should have included this in the above. Just wanted the bare essentials in there. I think this is whats tripping up my SQL as @D-Shih answer works perfectly but not for my solution. — JimmyPop13, Oct 29 '18 at 12:42
ah now I understand, yes you should have mentioned that in the question. Now the if statement also makes sense to me — GuidoG, Oct 29 '18 at 13:12
I think you simple should add another variable initialized to -1 and change the while loop from `while 1=1` to `while @RowsUpdated <> 0` and then set `@RowsUpdated` equal to @@ROWCOUNT in your loop. Then you can also drop the ugly break statement — GuidoG, Oct 29 '18 at 13:17

score 2 · Answer 1 · answered Oct 29 '18 at 12:10

Use temp variable to store @@ROWCOUNT value. After the test in IF line, this value is set to 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

This also works to my original question. Problem is I did not include the while loop I was using (updated above) and it loops over and over now. Any ideas how to work around that? — JimmyPop13, Oct 29 '18 at 12:46

score 2 · Answer 2 · answered Oct 29 '18 at 12:10

If you declare a value outer it can work, make sure your variable scope.

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

This works great, except (updated above) I am using a While and I think this is effecting the outcome as it gets stuck looping over and over. Any thoughts on this? — JimmyPop13, Oct 29 '18 at 12:45

score 2 · Answer 3 · answered Oct 29 '18 at 12:37

You can try below query to insert records affected in audit table with datetime as shown below.

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

Output is as below

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

Hope this will help you.

t-clausen.dk · Answer 4 · 2018-10-29T13:33:33.840

I dropped the first transaction and rearranged some parts. I have declared 2 variables in the beginning of the script. It seems you already have the @audit table variable, so you may want to leave out that part. The most significant part of this script is that it doesn't go into endless loop and only update rows without the flag set to 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

GuidoG · Answer 5 · 2018-10-29T13:55:24.750

2

I would not use while 1=1 but a more logical loop, like this.
This should never become an infinite loop and it should write correct in your audit table, without the need of a break construct.

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

edited Oct 29 '18 at 13:55

answered Oct 29 '18 at 13:30

GuidoG

11,359
6
44
79

First issue: this will go into endless loop,because the #list table will never be empty - unless it is empty from start. Second issue: The first transaction will never rollback anything. – t-clausen.dk Oct 29 '18 at 13:39
@t-clausen.dk That's what you get when copy/pasting code. Fixed the issues – GuidoG Oct 29 '18 at 13:42

Returning how many rows changed while IF statement is used?

5 Answers5