Systems Engineering and RDBMS

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:

Begin tran

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: