SQL SERVER 2008R2 Nested Transactions with RAISERROR -

we going through process of switching db2 sql server 2008r2 , i'm bit unfamiliar tsql. getting better understanding of occurring nice. we've created procedure called rethrowerror as:

create procedure rethrowerror  begin     -- return if there no error information retrieve.     if error_number() null         return;  print 'yo error';       declare          @errormessage    nvarchar(4000),         @errornumber     int,         @errorseverity   int,         @errorstate      int,         @errorline       int,         @errorprocedure  nvarchar(200);      -- assign variables error-handling functions      -- capture information raiserror.     select          @errornumber     = error_number(),         @errorseverity   = error_severity(),         @errorstate      = error_state(),         @errorline       = error_line(),         @errorprocedure  = isnull(error_procedure(), '-');      -- build message string contain original     -- error information.     select @errormessage = n'error %d, level %d, state %d, procedure %s, line %d, ' +                             'message: '+ error_message();  print 'yo doin something';       -- raise error: msg_str parameter of raiserror contain     -- 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.         );  print 'yo end';      return; end go 

the reason created procedure purely expand on errors in future without having touch procedures. i've added print lines debugging purposes.

my main question have procedure , on failure executes rethrowerror , i'll see messages

yo error yo doin yo end 

as expected.

create procedure dbo.a begin    set nocount on;     declare & set variables; begin try    begin transaction maintaintarget     stuff end try begin catch    exec rethrowerror;      if (xact_state()) = -1     begin         print             n'the transaction in uncommittable state. ' +             'rolling transaction.'         rollback transaction;     end;       if (xact_state()) = 1     begin          print             n'the transaction committable. ' +             'rolling transaction.'         rollback transaction;     end;       return -101; end catch; return; end  go 

however, we've created procedure executes many procedures , when nested procedure (ie procedure being called procedure b) fails messages see are

yo error yo doin 

i'm don't quite understand why last message no longer shows up.

the procedure b similar procedure little difference in catch.

create procedure dbo.b begin    set nocount on;     declare & set variables; begin try        stuff end try begin catch  commit;   return -101; end catch; return; end 

any on getting better understanding happening appreciated.

i allowed myself edit code mimic bahaviour, keep simple (your job, ;).

your proca works fine, because rethrowerror procedure being called inside catch block of proca , executes. in second case, still happens inside try block of procb! catch part of procb fires after raiserror in rethrowerror called.

this simple example demonstrates behaviour of try-catch:

begin try     select 1/0     print 'doesnt show - div error' end try begin catch     print 'oops'     select 1/0     print 'this 1 shows because in catch!' end catch 

and here's simplified code:

-- "proc b" start begin try     -- "proc a" start (works fine alone)     begin try         begin tran         select 1/0  --error     end try     begin catch         print 'yo error';         raiserror ('re rethrowerror', 16, 1)   --comment out , see happens         print 'yo end';          if (xact_state())=-1 or (xact_state())=1         begin             print n'rolling transaction.'             rollback transaction;         end     end catch   -- "proc a" ends end try begin catch     select error_message(), error_severity(), error_state() --     print 'outer catch';     commit; end catch; 

hope helps.