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
@@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
. . .
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With