Launch multiple stored procedures to run 'in the background' on SQL Server Express Edition

Question

Is it possible to run multiple stored procedures that run 'in the background'?

The stored procedures must be launched from a single, master stored procedure, in the same way that multiple worker threads are spawned. For example:

CREATE PROCEDURE MyLauncher
AS
BEGIN
    BEGIN
      @EXEC MyBackgroundSP01 -- Runs in parallel to the other 2
      @EXEC MyBackgroundSP02 -- Runs in parallel to the other 2
      @EXEC MyBackgroundSP03 -- Runs in parallel to the other 2
    END
END

Answer 1

No this isn't possible as you have described it. You could run multiple SQL Jobs which will execute the procedures concurrently/

Answer 2

This could depend on your hardware setup. For example, how many processors sit on the machine/server which hosts SQL Server?

Though the stored procs would run concurrently as mentioned in the previous post

Answer 3

According to this question you could try using the Service Broker

http://stackoverflow.com/questions/25460/asynchronous-stored-procedure-calls

Answer 4

If you run they in the same procedure, it will launch in the same thread (and in the same internal transaction, what can make the log very big).

Answer 5

Not with pure T-SQL. But you could write a little dotNET app to run them asynchronously easily enough, as long as you leave the connection option until all three are finished.

Answer 6

It is possible in SQL 2005 and later. Have look at http://rusanu.com/2009/08/05/asynchronous-procedure-execution/