Async Stored Procedure Call in T-SQL

Question

How one can make an async call to a stored procedure from another one?

Assume I have two stored procedures, SP1 and SP2 (this is a long running stored procedure, takes much time to execute, and doesn't return any result).

The stored procedure SP1 is defined like this:

CREATE PROCEDURE SP1
AS
BEGIN

  --custom business logic

  --CALL to SP2, but async

  EXEC SP2

END

How could you make a non-blocking/async call to SP like the above in SQL Server 2008/2012?

Answer 1

There was once I tried to achieve this by wrapping the stored procedure into Job, and then Calling the job in the procedure through sp_start_job system sp.

EXEC dbo.sp_start_job N'Job name' ;