Stored Procedure Template for Try/Catch with Transaction Handling  

Posted by ReelTym



set nocount on;
declare @trancount int = @@trancount;
begin try
    if @trancount = 0 begin tran
    else save tran ScriptSavePoint;
   
[code goes here]

    if @trancount = 0 commit;
end try
begin catch
    declare
        @error      int             = ERROR_NUMBER(),
        @message    varchar(4000)   = ERROR_MESSAGE(),
        @xstate     int             = XACT_STATE();
    if ( @xstate = -1 ) or ( @xstate = 1 and @trancount = 0 ) rollback
    if ( @xstate = 1 and @trancount > 0 ) rollback ScriptSavePoint;

    raiserror ('ERROR: %d : %s', 16, 1, @error, @message) ;
end catch