on Developer IT
See other posts from Developer IT
or by developerit
Published on Thu, 11 Mar 2010 23:03:12 +0000 Indexed on 2010/03/16 14:51 UTC
Read the original article Hit count: 9123
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.
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.
© Developer IT or respective owner