How to run multiple stored procedures in parallel?

  • I have a stored procedure which runs in database DB1

    This stored procedure will have other stored procedures inside it which should run in parallel.

    Create procedure db1.proc1

    AS

    use db2

    call proc2

    use db3

    call proc3

    use db4

    call proc4

    Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?

    Thanks

  • 1 method would be to create a separate job for Proc2, 3 and 4. Then Proc1 could run sp_start_job "proc2' etc...

  • Guras (9/30/2013)


    I have a stored procedure which runs in database DB1

    This stored procedure will have other stored procedures inside it which should run in parallel.

    Create procedure db1.proc1

    AS

    use db2

    call proc2

    use db3

    call proc3

    use db4

    call proc4

    Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?

    Thanks

    Can't force them to run in parallel afaik but it is easy enough to use try/catch so that each proc will still run. Something like this.

    Create procedure proc1

    AS

    begin try

    exec db2.dbo.proc2

    end try

    begin catch

    --do something so you know that proc2 failed

    end catch

    begin try

    exec db3.dbo.proc3

    end try

    begin catch

    --do something so you know that proc3 failed

    end catch

    begin try

    exec db4.dbo.proc4

    end try

    begin catch

    --do something so you know that proc4 failed

    end catch

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Service broker could be used to accomplish this. Read this article and maybe you could adapt your system to using this method.

    http://rusanu.com/2009/08/05/asynchronous-procedure-execution/



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • For what you are doing I think Sean's solution is the way to go. That said, you could run them in parallel in an SSIS package then have your proc run the SSIS package (either via sp_start_job if you setup the SSIS package as a job or via xp_cmdshell calling DTSEXEC)

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Though i am not 100 % sure but we cant exec anything in parallel way(NOt talking about parallelism here). behind the scene sql engine will treat it with asynchronous approach ( but yes there could be some conditional way to execute something if something happens)

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

  • If you have to run these in parallel, then I would suggest using PowerShell. You can spawn multiple threads to have them all firing at the same time. I don't have an example at hand, but you should be able to search one up pretty quick.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I used to run it using a set of sp_OACreate\sp_OAMethod procedures. I don't have my procedure around, since I didn't use for the last few years, but I am sure you will be able to find it on the web...

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

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