How can I run sql server stored procedures in parallel?

Question

Post reply

How can I run sql server stored procedures in parallel?

peter.hegarty

SSC Enthusiast

Points: 191
More actions
November 11, 2008 at 2:30 am

#154907

I want to do something like:
exec sproc1 and sproc2 at the same time
when they are both finished exec sproc3
I can do this in dts. Is there a way to do it in transact sql? Or is there a way to do it with a batch script (eg vbs or powershell)?

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

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

As an alternative, you could do this with .Net, but it would be a fair amount of work.

I am just wondering why you don’t want to use DTS, as that would be the simplest.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 2

Will below work?

a. Wrap each sp [sp1 and sp2] within a function [f1 and f2]. Each function will just execute sp [f1 will execute sp1 and f2 will execute sp2], and then return a value (like 1)

b. Write another sp [sp4], which will execute functions 1 and 2, each time setting a value (let's say from initial 0 to 1), as a function output

c. Place IF within sp4 to say, if output1 = 1 and output2 = 1, exec sp3

peter.hegarty SSC Enthusiast Points: 191 More actions · Answer 3

Thats a good idea.

I haven't used functions before.

I tried it out, but it seems that you can only call extended stored procedures from UDFs

- Peter

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 4

Another option is to use stored procedures (instead of functions i described in the original reply) with an OUTPUT parameter. When you execute those sps, the output parameters will be set much same the same way.

For example:

DECLARE @Decide1 BIT

DECLARE @Decide2 BIT

EXECUTE SP_1 @ Decide1 OUTPUT

EXECUTE SP_2 @ Decide2 OUTPUT

Will this work?

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 5

Do you really need them to execute at the same time or just both execute and return a value before sproc 3?

The technique above from sql_er will work if they just both need to execute with some specific return value.

peter.hegarty SSC Enthusiast Points: 191 More actions · Answer 6

Yeah I'm looking for them to happen in parallel.

I don't know much about functions, but it that suggestion was promising because I thought maybe I could do something like:

select funcion1, function2

and these would get executed in parallel.