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