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 http://www.developerit.com/2010/03/16/good-sql-error-handling-in-strored-procedure
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 http://www.developerit.com/
Developer IT on Wordpress 
What do you do in the code to catch the error message when that happens, though?
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?