A very common mistake in error handling code
Posted by decipherinfosys on January 11, 2009
This last week while we were helping one of our clients roll out their application on SQL Server 2005, we also reviewed their DB code. One of the common mistakes made was how the error handling was done. Since this client migrated from version 2000 to version 2005 without making many code changes, they were still using @@ERROR and RAISERROR in their SQL code instead of using TRY…CATCH blocks.
Regardless, the issue is that @@ERROR returns an error only if the previous statement encounters an error. It is not meant for an entire batch. Since it is re-set on each statement that is executed, one has to have logic to check it after every statement execution. Pretty cumbersome but prior to SQL Server 2005, it had to be done.
The previous code (showing pseudocode only) was like this:
insert into a table here
update another table here
select values from some tables here
select @error_var = @@error
if @error_var <> 0 GOTO ErrHandler
In the above code, if the first two statements return an error, the error handler won’t be able to catch that error. It will only catch the error if the select statement runs into an issue. In order to resolve this, the @@ERROR check needs to be made after each statement. If you are using SQL Server 2005 and above, you should really move to TRY…CATCH block usage.