Jun 072010
Using @@rowcount is nice at times to get the number of rows affected by the last SQL statement. However sometimes you get an unexpected result.
One such situation is with the print statement which is often used as debugging statement in a query.
If looking at the SQL statement:
SELECT 1
UNION
SELECT 2
SELECT @@ROWCOUNT
Then the @@rowcount naturally will return the value 2.
However if we insert a print statement just before the rowcount like this:
SELECT 1
UNION
SELECT 2
PRINT ‘test’
SELECT @@ROWCOUNT
Rowcount now suddenly is 0.
That means the print statement changes the rowcount despite it being just a print to the console window.
Something to be aware of if relying on @@rowcount.
Hi Allan,
Except for the EXECUTE command, every other SQL statement that occurs after a statement that sets @@ROWCOUNT will reset the value.
Here is a note from Microsoft’s TechNet:
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’.
Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.
Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.
EXECUTE statements preserve the previous @@ROWCOUNT.
PRINT is one of the statements that reset ROWCOUNT to 0.
What we always did after EVERY statement that was executed that might contain a ROWCOUNT we needed was to use a SET @local_variable assignment to capture the preceding ROWCOUNT. The SET itself then reset ROWCOUNT to 1, but we now had a permanent value that wouldn’t change for any number of statements until the next actual assignment to that variable. I think that is a good programming habit when using SQL Server.