How to run an unknown number of stored procs in parallel

Question

I am looking for something like this. Although the syntax is wrong, it demonstrates the principle

foreach(int i in myIntArray)
{

execute mystoredProc i;//this should kick off the proc and go onto next one without waiting for a return value

}

These stored procs are called from a Windows application. I am a little skeptical of creating numerous threads at the application end. I would rather do the threading at the SQL server end. I am open to using SSIS.

check out the divide and conquer section of this answer http://stackoverflow.com/questions/4129071/optimal-mysql-settings-for-queries-that-deliver-large-amounts-of-data/4144514#4144514 — Jon Black, May 17 '12 at 22:13

score 1 · Accepted Answer · answered May 17 '12 at 22:10

You can't do what you're asking for directly.

What you can do is to fire up n threads, and then each thread open up it's own connection, and each connection run it's own SQL query. Each thread will then wait for it's query to return. You can't do this in just one thread.

This also means that you can't do it natively within T-SQL.

You could write a CLR routine that launches multiple threads, and repeat the above process. So enabling your T-SQL to call your CLR code and the CLR deal with the concurrency problem.

But the standard practice for this really is to have multiple client side threads.

How to run an unknown number of stored procs in parallel

1 Answers1