Try/ Catch Usage in Stored Procedures

Create Procedure XXX

 AS

 Begin

 Begin Try

  

Set nocount on

--Declaration Section

Begin Transaction Trans1

---Your Code 

Commit Transaction Trans1

  

End Try

Begin Catch

Declare

    @ErrorMessage    NVARCHAR(4000),

    @ErrorNumber     INT,

    @ErrorSeverity   INT,

    @ErrorState      INT,

    @ErrorLine       INT,

    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that it capture information for RAISERROR.

Select

    @ErrorNumber = ERROR_NUMBER(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @ErrorLine = ERROR_LINE(),

    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original error information.

Select

@ErrorMessage = 

    N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ ERROR_MESSAGE();

If XACT_STATE() <> 0 Rollback Transaction Trans1

-- Raise an error: msg_str parameter of RAISERROR will contain the original error information.

Raiserror

     (

    @ErrorMessage,

     @ErrorSeverity,

     1,              

     @ErrorNumber,    -- parameter: original error number.

     @ErrorSeverity,  -- parameter: original error severity.

     @ErrorState,     -- parameter: original error state.

     @ErrorProcedure, -- parameter: original error procedure name.

     @ErrorLine       -- parameter: original error line number.

     );

 End Catch;

END