I'm coding a stored procedure which will be the control mechanism for a series of ETL subprocesses. This control procedure will run through several loops, monitoring various states, and executing these subprocess stored procedures when needed. The challenge is, I'd like to execute each of these subprocess stored procedures in their own session, so that they can run in parallel. I don't want the control procedure to wait for the completion of any of the subprocess stored procs.
I think I could do this with xp_cmdshell calling SQLCMD, but that seems pretty kludgy, and I'd have to convince our admins to enable xp_cmdshell. I think I could also create, execute, and delete a SQL Agent job on the fly, but that seems even more kludgy. Is there a simpler way to execute a stored procedure in a new SPID?