[Exception Handling ]Template for Stored Procedure – In Sql Server

In SQL Server-Exception Handling Template for Stored Procedure

This article, in the series of articles on Exception Handling in SQL Server. Below is the complete list of articles in this series.

Exception Handling Template

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 its correctness and then use it 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