Let’s say you’re writing T-SQL code and need to make sure that your query returns one and only row. If no records returned – an error message needs to be shown. If more than one record is returned – another error message needs to be show. The code goes something like this:
-- ... query runs here and returns row
IF @@ROWCOUNT = 0 RAISERROR('No records found', 18, 1)
ELSE IF @@ROWCOUNT > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
Now if your query returns 3 records you’d expect it to trip the second IF statement and raise error with correct message. Unfortunately this doesn’t happen. The reason being – the first IF statement resets @@ROWCOUNT to 0. So, to avoid this we need to preserve the @@ROWCOUNT value in a local variable:
DECLARE @iRowCount int
-- ... query runs here and returns row
SET @iRowCount = @@ROWCOUNT
IF @iRowCount = 0 RAISERROR('No records found', 18, 1)
ELSE IF @iRowCount > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
This way count of rows returned by the query is saved and is not affected by any following statements.
Weird behaviour but it’s good to know it for the future.
Good one! For people who need more info, here is a snippet from MS:
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.
EXECUTE statements preserve the previous @@ROWCOUNT.
Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.
Ref: http://technet.microsoft.com/en-us/library/ms187316%28v=sql.105%29.aspx