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?

1. 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:

1. 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.

Script:

Select 1 as output
union All
select 2 as output

Declare @Flag bit
Set @Flag=1

select @@ROWCOUNT as TotalRecords

OutPut:

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