Good SQL Error Handling in Stored Procedure

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 another "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.

BEGIN TRANSACTION;
BEGIN TRY

-- Code in transaction go here

COMMIT TRANSACTION;

END TRY BEGIN CATCH

    -- Rollback on error
    ROLLBACK TRANSACTION;

    -- Raise the error with the appropriate message and error severity
    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
    SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
    RAISERROR(@ErrMsg, @ErrSeverity, 1);

END CATCH;

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.

Original post URL: http://sp.nitriques.com/sites/blog/Lists/Posts/Post.aspx?ID=15

Category: CodeProject

Published: 5/7/2009 11:22 PM