CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
humblecoder
humblecoder

Reputation: 507

Async Stored Procedure Call in T-SQL

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?

Upvotes: 11

Views: 25606

Answers (2)

Karol J
Karol J

Reputation: 31

Blockquote
Works as long as there are no arguments. – RoastBeast Dec 22 '15 at 17:30

Here's version with passing parameters

  declare @variable -- job name
declare @command -- command

set @command = 'select * from table where data='+@variable

exec msdb..sp_add_job
@job_name =@variable,
@enabled=1,
@start_step_id=1,
@delete_level=1 --Job will delete itself after success

exec msdb..sp_add_jobstep
@job_name=@variable,
@step_id=1,
@step_name='step1',
@command=@command

exec msdb..sp_add_jobserver
@job_name = @variable,
@server_name = 'yourserver'

exec msdb..sp_start_job
@job_name=@variable

Upvotes: 3

Sonam
Sonam

Reputation: 3466

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' ;

Upvotes: 6

Related Questions