Exception Handling Template for Stored Procedure – In Sql Server

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

6 thoughts on “Exception Handling Template for Stored Procedure – In Sql Server

    1. 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… 🙂

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *