Good SQL error handling in Strored Procedure

11 Mar

When writing SQL procedures, it is really important to handle errors cautiously. Having that in mind will probably save your efforts, time and money. I have been working with MS-SQL 2000 and MS-SQL 2005 (I have not got the opportunity to work with MS-SQL 2008 yet) for many years now and I want to share with you how I handle errors in T-SQL Stored Procedure. This code has been working for many years now without a hitch.

N.B.: As antoher “best pratice”, I suggest using only ONE level of TRY … CATCH and only ONE level of TRANSACTION encapsulation, as doing otherwise may not be 100% sure.

See the full article and source code at

In conclusion, I will just mention that I have been using this code with .NET 2.0 and .NET 3.5 and it works like a charm. The .NET TDS parser throws back a SQLException which is ideal to work with.
Published on

2 Responses to “Good SQL error handling in Strored Procedure”

  1. Isaac January 18, 2011 at 4:39 pm #

    What do you do in the code to catch the error message when that happens, though?

    • developerit January 18, 2011 at 9:31 pm #

      I normally code in .NET and the TDS Parser raises a SQLException.

      So I code another try catch or handles the event of my DataSource object.

      What language are you usign?

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: