SQL Server: Checking @@ROWCOUNT and @@ERROR

The following SQL contains a subtle bug that will always result in the text “No Rows Affected” being output.

SELECT ‘Hard Coded Row’
IF(@@ERROR <> 0)
   Print ‘Error Occured’
ELSE IF(@@ROWCOUNT = 0)
   Print ‘No Rows Affected’

The error occurs because the reference to @@ERROR in the first “IF” statement counts as a SQL statement; resetting the value held in @@ROWCOUNT.   As the second “IF” statement checking @@ROWCOUNT is only evaluated if the first “IF” statement (@@ERROR) it will always return true!   Note: Reversing the order of the two IF statements would hide any potential errors, as @@ERROR would be reset upon checking @@ROWCOUNT.

The safest way to evaluate this statement is to SELECT the contents of @@ERROR and @@ROWCOUNT into local variables within a single statement and then check the values of the local variables, in other words:

DECLARE @ErrorCode INT
DECLARE @RowsAffected INT
SELECT ‘Hard Coded Row’
SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT
IF(@ErrorCode <> 0)
   Print ‘Error Occured’
ELSE IF(@RowsAffected = 0)
   Print ‘No Rows Affected’

Problem solved!