Can someone explain this strange value of @@ROWCOUNT?

I have noticed the following strange situation, but cannot find any explanation. The @.rows variable is 1 , even though the @.@.ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.err = @.@.ERROR
set @.rows = @.@.ROWCOUNT

However, if I set @.rows before @.err then the @.rows is correctly set to 0. (as in script below).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.rows = @.@.ROWCOUNT
set @.err = @.@.ERROR

Can someone please explain why this is happenning?

In the first instance, @.@.ROWCOUNT is reporting on the statement immediately above it, which is the @.@.ERROR check. Please realize, however, that in your second instance, your @.@.ERROR check is now happening against your @.@.ROWCOUNT check.

To get around this issue, it's better practice to use a statement like this,making sure to reference @.@.ERROR and @.@.ROWCOUNT in this specific order:

SELECT @.err = @.@.ERROR, @.rows = @.@.ROWCOUNT

|||

Thanks for the explanation.

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

|||

sun21170:

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

Thank you, you are correct, the order does not matter!