Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
First off, great question +1. I have to agree with you, just due to the fact that the owning procedure should be handling its own errors. I don't see the benefit of shuffling that off to a calling proc. For instance, if Proc A is called by Proc B, Proc B has to handle the return code from Proc A. If Proc A is also called by Proc C, then Proc C has to handle the return code from Proc A. It's duplicate the work, if instead you just had Proc A handle internal errors there. Just my opinion. –  Thomas Stringer May 9 '12 at 13:01
    
@Shark I agree but the folks who did this aren't dummies so I'm thinking there is a reason for it that I'm just not seeing. –  JNK May 9 '12 at 13:11
    
I prefer your approach, which is similar to what I have in the linked question: clear, obvious, easy to find out where the fan got the brown stuff on it –  gbn May 9 '12 at 13:17

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer
    
This is the same your answer in @JNK's linked question, no? –  gbn May 9 '12 at 13:19
1  
Well that's embarassing. I didn't follow the link. One slight difference is that this answer deals with nested procedure calls. –  datagod May 9 '12 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.