Post reply

SQL stored procedure that calls multiple stored procedures in one dataset

  • December 2, 2008 at 9:17 am

    #198425

    Please Help,

    I need a stored procedure that calls other stored procedures. This stored procedure will execute the 12 other SP's. Is there a way to do this with any command?? Thanks a lot...Some example SQL would be appreciated!!

  • December 2, 2008 at 9:25 am

    #906295

    You can call a stored proc from another proc by using the standard EXEC funciton.

    EXEC YourProc

    Be aware though that you will have to be able to handle errors from the nested procedures and roll-back the affected transactions from the calling procedure.

  • December 2, 2008 at 9:37 am

    #906306

    Steve thanks for your quick reply.

    So I just create another stored procedure...

    CREATE Procedure blah blah

    (

    @blah blah

    )

    AS

    EXEC MyPRoc

    Is that what you are saying?...thanks

  • December 2, 2008 at 9:51 am

    #906327

    Basically, yes. So your proc that calls other procs would be something like

    CREATE Procedure blah (@blah)

    AS

    EXEC MyPRoc1

    EXEC MyPRoc2

    EXEC MyPRoc3

    EXEC MyPRoc4

    ...

    If the procs can fail and throw errors, you need to handle those errors in the calling proc. If they're running within a transaction, you need to manage the possible rollbacks.

    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

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

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