Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why PRINT affects @@rowcount

I recently noticed that while trying to loop through the rows of a table my loop wouldn't run based on if I had a PRINT statement before the the loop condition involving @@ROWCOUNT.

declare @s int;

select top 1 @s=sequence from myTable order by sequence;

while @@rowcount > 0
begin
    print @s
    select top 1 @s=sequence from myTable where sequence > @s order by sequence;
end

print @s

The above code prints what is expected; a sequence of numbers for each row in the table, but,

declare @s int;

select top 1 @s=sequence from myTable order by sequence;
print @s
while @@rowcount > 0
begin
    print @s
    select top 1 @s=sequence from myTable where sequence > @s order by sequence;
end

print @s

only prints the first value of sequence twice (one for each PRINT outside of the loop).

I tried reading up on the PRINT statement but found nothing on it affecting @@ROWCOUNT.

My question is, why does this PRINT affect @@ROWCOUNT and why isn't it more clearly documented because this can cause some bugs which are hard to debug?

UPDATE

After more research I did find

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

from Microsoft's @@ROWCOUNT docs

like image 538
Archmede Avatar asked Jan 26 '23 10:01

Archmede


1 Answers

@@ROWCOUNT always refers to the previous executed statement, even print.

That is why code using @@ROWCOUNT almost always assigns the value to a variable:

declare @s int;
declare @rowcnt int;

select top 1 @s = sequence from myTable;
set @rowcnt = @@ROWCOUNT;

while @rowcnt> 0
    . . .
like image 160
Gordon Linoff Avatar answered Feb 17 '23 11:02

Gordon Linoff