Post reply

Error handling in called stored procedures

  • April 14, 2015 at 3:49 am

    #316637

    Hi,

    I have one main stored procedure. It calls other 10 stored procedures by giving input parameters.

    Do I want to implement Begin Try/Begin catch in each sub procedures or in main proceudre only.

    Please suggest how to achieve?

    Appreciate your help.

    Regards,

    Bala

  • April 14, 2015 at 3:51 am

    #1790705

    Honestly, it depends on your requirements.

    If an error in any of the 10 procedures means that the entire processing should be aborted, then error handling in the caller. If an error in one of the procedures means just that procedure needs to be aborted and the other nine should still run, then error handling in the called procedures.

    There's no one way, it depends on how the code must behave in the case of an error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 14, 2015 at 4:02 am

    #1790707

    Usually you want to catch errors in each stored procedure and re-throw them to calling procedures. I published my stored proceedure code template here: http://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/

    Hope this helps

    -- Gianluca Sartori

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply