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.

[code language=”sql”] SELECT ‘Hard Coded Row’ IF(@@ERROR <> 0) Print ‘Error Occured’ ELSE IF(@@ROWCOUNT = 0) Print ‘No Rows Affected’ [/code]

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:

[code language=”sql”] 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’ [/code]

Problem solved!