Error handling in container procedures

Question

I'm aware of and use the techniques in this question.

In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum.

For me personally, I normally use TRY/CATCH in each level of the procedures. This will sometimes result in multiple duplicate error messages but it's clear what happened and in what proc.

I have some coworkers who take a different approach. Namely: - Have each stored proc issue a return code - Evaluate the return code for each stored proc after execution - Raiserror in the outermost proc if a bad return code is received

I believe they do this so they can get the actual line number from the erroring procedure (instead of having it return the line number of the RAISERROR in the CATCH block).

To me this seems like it would be harder to implement and maintain, but I'm not an expert on SQL Server error handling, so I thought I would bring it up here.

Is there any material advantage to either method for handling errors in container procedures?

Answer 1

I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the 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 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

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

This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.

I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple THROW; (no other arguments) to re-raise the original exception.

Answer 2

I use a mix of both. I use Try/Catch whenever I am performing an insert/update/delete, or even if I am doing a critical select (e.g. one that absolutely must return data, or a select from a remote dataset)

In each stored procedure I have an error handling section that calls a function called GetErrorInfo which gathers the relevant error message data.

  select  @output =  ' (ERROR: ' + isnull(CAST(ERROR_NUMBER()   AS VARCHAR(10)),'(UNKNOWN ERROR)')
                  +  ','         + isnull(CAST(ERROR_SEVERITY() AS VARCHAR(10)),'(UNKNOWN SEVERITY)')
                  +  ','         + isnull(CAST(ERROR_STATE()    AS VARCHAR(10)),'(UNKOWN ERROR_STATE)')
                  +  ' Msg: '    + isnull(ERROR_MESSAGE(),                      '(UNKNOWN ERROR_MESSAGE)')
                  +  ' Line: '   + isnull(CAST(ERROR_LINE()     AS VARCHAR),    '(UNKNOWN ERROR_LINE)')
                  +  ' Proc: '   + isnull(ERROR_PROCEDURE(),                    '(UNKNOWN ERROR_PROCEDURE)')
                  + ') '
  RETURN @output

This data is used as part of a RAISERROR with LOG statement, then the stored procedure exits with return code -1.

The calling procedure always checks the return codes, and if it is less than 0, it will raise it's own error.

You get a cascading effect when there are several nested procedure calls, but the error message is clear about what happened, and where.