Good SQL Error Handling in Stored Procedure






2.25/5 (3 votes)
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