sql server - Rollback transaction from called stored procedure -

i have simple scenario: logger procedure , main procedure logger called. trying rollback transaction inside logger started in main, getting errors. not sure why. here 2 procs , error message receive:

create procedure splogger begin     if @@trancount > 0     begin        print @@trancount        rollback     end end go  create procedure spcaller begin     begin try        begin transaction           raiserror('', 16, 1)        commit transaction     end try     begin catch        exec splogger     end catch end go  exec spcaller 

1 msg 266, level 16, state 2, procedure splogger, line 15 transaction count after execute indicates mismatching number of begin , commit statements. previous count = 1, current count = 0.

1) error message clear: number of active txs @ end of sp should same number of active txs @ beginning.

so, when @ execution of dbo.splogger begins number of active txs (@@trancount) 1 if execute within sp rollback statement this'll cancel all active txs , @@trancount becomes 0 -> error/exception

2) if want avoid writing if @@trancount ... rollback within every catch block of every user sp don't it. call dbo.splogger within catch block after rollback.

3) if have call sps other sp using txs use following template (source: rusanu's blog)

create procedure [usp_my_procedure_name] begin     set nocount on;     declare @trancount int;     set @trancount = @@trancount;     begin try         if @trancount = 0             begin transaction         else             save transaction usp_my_procedure_name;          -- actual work here  lbexit:         if @trancount = 0                commit;     end try     begin catch         declare @error int, @message varchar(4000), @xstate int;         select @error = error_number()                                    , @message = error_message()                                    , @xstate = xact_state();         if @xstate = -1             rollback;         if @xstate = 1 , @trancount = 0             rollback         if @xstate = 1 , @trancount > 0             rollback transaction usp_my_procedure_name;          throw;     end catch    end 

with few small changes:

a) set xact_abort on

b) call dbo.splogger within catch block when there @@trancount = 0:

if @@trancount = 0 begin     exec dbo.splogger ... params ... end throw -- or raiserror(@message, 16, @xstate) 

why ? because if dbo.splogger sp insert rows dbo.dbexception table when 1 tx active in case of rollback sql server have rollbacl these rows.

example:

sp1 -call-> sp2 -call-> sp3                          |err/ex -> catch & raiserror (no full rollback)               <-----------               |err/ex -> catch & raiserror (no full rollback)  <------------- |err/ex -> catch & full rollback & splogger       

4) update

create proc testtx begin     begin tran -- b     rollback -- c end  -- d go  -- test begin tran -- - @@trancount = 1 exec dbo.testtx      /*     number of active txs (@@trancount) @ begining of sp 1     b - @@trancount = 2     c - @@trancount = 0     d - execution of sp ends. sql server checks & generate err/ex         transaction count after execute indicates mismatching number of begin , commit statements. previous count = 1, current count = 0.     */ commit -- e - because @@trancount 0 statement generates  

another err/ex commit transaction request has no corresponding begin transaction. -- end of test

5) see autonomous transactions: requires sql2008+.

an autonomous transaction nested transaction inner transaction not affected state of outer transaction. in other words, can leave context of current transaction (outer transaction) , call transaction (autonomous transaction). once finish work in autonomous transaction, can come continue on within current transaction. done in autonomous transaction done , won’t changed no matter happens outer transaction.