Executing if @@rowcount>0 resets @@rowcount to 0. Why?

Question

Link

@@Rowcount is used to inform the number of rows affected for the last select,insert,update or delete statements

declare @row int select 100 if @@rowcount>0  set @row=@@rowcount ...

The above will return 0 because as soon as if @@rowcount>0 is executed it is reset to 0 as it doesn't return any rows. So always assign to variable first

Why does statement if @@rowcount>0 reset @@rowcount to 0? Isn't the value of @@rowcount affected only by select,insert,update and delete statements?

thank you

Answer 1

It is affected by the last statement. Like this SET statement

Declare @row int
select 100 union all select 200 union all select 300
set @row = @@rowcount;
SELECT @row, @@rowcount

If you read the actual Microsoft SQL Server Docs on MSDN, it gives examples of what statements affect @@ROWCOUNT. For example "such as" implies other statements like IF will also set it to zero

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