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  

This entry was posted on Wednesday, May 30, 2012 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .
Filed under ,

0 comments

Newer Post Older Post