Start stored procedures sequentially or in parallel

Answer 1

Create a couple of SQL Server agent jobs where each one runs a particular proc.

Then from within your master proc kick off the jobs.

The only way of waiting that I can think of is if you have a status table that each proc updates when it's finished.

Then yet another job could poll that table for total completion and kick off a final proc. Alternatively, you could have a trigger on this table.

The memory implications are completely up to your environment..

UPDATE: If you have access to the task system.. then you could take the same approach. Just have windows execute multiple tasks, each responsible for one proc. Then use a trigger on the status table to kick off something when all of the tasks have completed.

UPDATE2: Also, if you're willing to create a new app, you could house all of the logic in a single exe...

Answer 2

You do need to move your overnight sprocs to jobs. SQL Server job control will let you do all of the scheduling you are asking for.

Answer 3

You might want to look into using DTS (which can be run from the SQL Agent as a job). It will allow you pretty fine control over which stored procedures need to wait for others to finish and what can run in parallel. You can also run the DTS package as an EXE from your own scheduling software if needed.

NOTE: You will need to create multiple copies of your connection objects to allow calls to run in parallel. Two calls using the same connection object will still block each other even if you don't explicitly put in a dependency.