This is the fourth article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.
Part I: Exception Handling Basics – MUST Read Article
Part II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template
Exception Handling Template
Based on my analysis, below is the exception handling template one should be using for proper error handling in Sql Server. Note the THROW statement works only in Sql Server 2012. You can validate it’s correctness and then use in your development work.
CREATE PROCEDURE dbo.ErrorHandlingTemplate AS BEGIN BEGIN TRY SET NOCOUNT ON SET XACT_ABORT ON -- Code Which Doesn't Require Transaction BEGIN TRANSACTION -- Code which Requires Transaction COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 ROLLBACK TRAN -- Do the Necessary Error logging if required -- Take Corrective Action if Required THROW --RETHROW the ERROR END CATCH END
So this template does not seem to handle nested transactions (as described at https://msdn.microsoft.com/en-us/library/ms188378.aspx), or am I missing it?
I think so , but if you modify this template, you can use it for nested transactions too. In the link that you shared, msdn creates a savepoint for distinguish the transaction effects from operations which already complated, this means that is you get an error in the procedure you can go back to the part of the transaction where this error not occured. If you implement this idea to this template you can write rollback to the begining of the catch block. I hope this helps… 🙂
Hi,
Very informative blog.Excellent .
My question is why use SET XACT_ABORT ON and Begin Transaction both in same proc ?
I think using only Begin Transaction along with Begin Catch will do,there is no need of SET XACT_ABORT ON. Please clarify my doubts.
Fantastic! this is exactly what i was looking for. Thank you very much