Few days back someone ask to me why a sql server global variable @@RowCount produce different output for different-different set of queries in store procedure.
Below is a simplified version of the query, can you tell why @@Rowcount will be 0?
- When Print Statements used before accessing the @@RowCount Variable.
Think what will be the output produce from print and select statements which are used at line number 12.
Are you thinking about 3 as there are 3 entries in a table.if you are thinking in this direction then it is completely wrong.
Answer:if you run complete query then value of variable @showcount will be 0.
Why:because print resets the value of @@ROWCOUNT to 0 and the same value assigned to @showcount parameter.So that @Showcount returns 0 .
so it is recommended that print statements should not be used before @@RowCount variable.if you are doing so then you always get surprise output which will not be as per expectation.
So best practices says,do not use any other query before @@RowCount variable and use local variables to hold the value of @@RowCount.Do not include global variables directly in your business logic.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Generic Text’.
Correct way to use @@RowCount:
If you want to store the rows that were affected by A DML statement then you need to grab @@ROWCOUNT immediately after the DML statement. There can’t be any code between the DML statement and your code that stores @@ROWCOUNT into a variable.
Here i am using same query as i mentioned top of the article with little bit changes to make query more feasible.
Now you can see that output is 3 and it is as per expectations. Because i have moved print statement just after the reading the @@RowCount variable.
There are more intersting facts about @@RowCount variables:
- IF statement also resets the @@ROWCOUNT and you get back 0.Try below query:
DECLARE @ShowCount INT SELECT 1 UNION all SELECT 2 if 1=1 SELECT @ShowCount = @@ROWCOUNT SELECT @ShowCount
2. Set Statements also reset the @@ROWCOUNT and you get back 1.
Select 1 as output
select 2 as output
Declare @Flag bit
select @@ROWCOUNT as TotalRecords
Again Surprise output,as per the select statement @@Rowcount should retuns 2 becuase UNION ALL used with both select statements but @@RowCount returns 1.
this happens due to Set statement is used just before accessing the value of @@RowCount.So set statement reset the counter to 1 so that different output produce by the sql engine.
There are many more statements for which @@ RowCount differently as per the expectation.
Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0