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
COMMIT TRANSACTION;
END TRY BEGIN CATCH
ROLLBACK TRANSACTION;
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
I am the general director of Nitriques Solutions inc. We build software and web solutions for enterprises using .NET 2.0 and 3.5, MS-SQL Server 2005 running on various Server 2003.
We offer a variety of products, from simple corporate web site with CMS to complete integration of intranets.
I have been working with asp et MS-SQL since 2002. I am particularly interested in web development 2.0, ajax, css, xhtml, asp.net, jQuery, Open Social and other web technologies.
In my spare time (when this concept exist in my life), i like to snowboard, skydive or free fall in wind tunnels and learning cool new stuff about anything.
Please feel free to contact me. http://www.nitriques.com