Skip to main content

Posts

Showing posts from November, 2010

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 = @@ROWCOU…