SQL SERVER 2008R2 Nested Transactions with RAISERROR

Question

We are going through a process of switching from DB2 to SQL Server 2008R2 and I'm a bit unfamiliar with TSQL. Any help getting a better understanding of what is occurring would be nice. We've created a procedure called RethrowError as:

CREATE PROCEDURE RethrowError 
AS
BEGIN
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;
 PRINT 'yo error'; 

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber     = ERROR_NUMBER(),
        @ErrorSeverity   = ERROR_SEVERITY(),
        @ErrorState      = ERROR_STATE(),
        @ErrorLine       = ERROR_LINE(),
        @ErrorProcedure  = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build 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();
 PRINT 'yo doin something'; 

    -- 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.
        );
 PRINT 'yo end'; 

   RETURN;
END
GO

The reason we created the procedure is purely to expand on errors in the future without having to touch all the procedures. I've added some PRINT lines for debugging purposes.

My main question is that we have procedure A and on failure it executes RethrowError and I'll see the messages

yo error
yo doin something
yo end

as expected.

CREATE PROCEDURE dbo.A
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE & SET VARIABLES;
BEGIN TRY
   BEGIN TRANSACTION MaintainTarget

   DO SOME STUFF
END TRY
BEGIN CATCH
   EXEC RethrowError;

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;


    IF (XACT_STATE()) = 1
    BEGIN

        PRINT
            N'The transaction is committable. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

     RETURN -101;
END CATCH;
RETURN;
END

GO

However, we've created a procedure that executes many procedures and when a nested procedure (ie procedure A being called by procedure B) fails the only messages I see are

yo error
yo doin something

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

The procedure B is similar to procedure A but with a little difference in the catch.

CREATE PROCEDURE dbo.B
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE & SET VARIABLES;
BEGIN TRY    
   DO SOME STUFF
END TRY
BEGIN CATCH
 COMMIT;

 RETURN -101;
END CATCH;
RETURN;
END

Any help on getting a better understanding what is happening would be appreciated.

Answer 1

I allowed myself to edit your code to mimic the bahaviour, but keep it simple (your job, actually ;).

Your procA works fine, because RethrowError procedure is being called inside CATCH block of procA and everything executes. But in your second case, it all still happens inside TRY block of procB! So CATCH part of procB fires immediately after RAISERROR in RethrowError is called.

This simple example demonstrates this 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 one shows because its in CATCH!'
end catch

And here's your 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 from RethrowError', 16, 1)   --comment this out and see what happens
        print 'yo end';

        IF (XACT_STATE())=-1 or (XACT_STATE())=1
        BEGIN
            PRINT N'Rolling back 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 this helps.