How to Shcedule set of SP,executing sequencly

  • Hi,

    I want Scedule the Set of Stored Procedures

    And if any one SP gets errors then next one should gets exceute.

    For Example,

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    The Above are the three SP's,

    I want to Sheduled it to execute it sequencely,

    and if in any one errors comes then next one should gets executed.

    Thanks in Advance.!

  • Lookup "IF", "@@ERROR", and "RETURN" in Books Online.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Create a job named J. Schedule each call as a seperate Step.

    As a default, the error in the Stored Procedure will be returned to the job.

    Use the conditions on the job step (success or failrue) to determine the execution path.

    The same thing can be done with SSIS.

    I guess what is the goal of this process. To be scheduled or to be called on demand?

    John Miner
    Crafty DBA
    www.craftydba.com

  • Hi John,

    Thanks for the reply,

    But I want to excute this SP Step by Step,

    And If in any one of the SP error comes then next one should be executed.

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    Can You please provide the steps or link to do the same

    Thanks in Advance!

  • avdhut.k (12/16/2012)


    Hi John,

    Thanks for the reply,

    But I want to excute this SP Step by Step,

    And If in any one of the SP error comes then next one should be executed.

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    Can You please provide the steps or link to do the same

    Thanks in Advance!

    this can be done with the help of JOB see http://msdn.microsoft.com/en-us/library/ms191439.aspx

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Again, a job is one way to do this.

    See "Set Job Step Success or Failure Flow" - http://msdn.microsoft.com/en-us/library/ms177461.aspx - Conditions for optional execution.

    I tried to elaborate more on the tasks.

    Psuedo Code:

    Create Job A

    Create Step 1, Call Stored Procedure 1

    Create Step 2, Call Stored Procedure 2

    Create Step 3, Call Stored Procedure 3

    Here is where all the magic happens! Look at Job Step Properties dialog box, select the Advanced page.

    Step 1 - On success, exit with success code.

    Step 1 - On Failure, goto step 2

    Step 2 - On success, exit with success code.

    Step 2 - On Failure, goto step 3

    Step 3 - On success, exit with success code.

    Step 3 - On Failure, exit with failure.

    This should execute one, two or three steps depending upon the return value of each stored procedure call.

    Sincerely

    John Miner
    Crafty DBA
    www.craftydba.com

  • Thank You Very Much!

Viewing 7 posts - 1 through 6 (of 6 total)

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