Asynchronous Execution of T-SQL command, MSSQL

Sometimes I need execution of some SQL command or stored procedure not in-process, but as Asynchronous task, especially when I need to call long running SQL command inside a trigger or another stored procedure (for example, large web cache deleting after data update).

I created the stored procedure using job object of SQL server agent. You can run the sql command asynchronously directly from SQL, from another stored procedure, task or another sql server command or from external ASP/ASP Net application.

/*
sp_async_execute - asynchronous execution of T-SQL command or stored prodecure
2012 Antonin Foller, Motobit Software, www.motobit.com
*/
CREATE PROCEDURE sp_async_execute(@sql varchar(4000), @jobname varchar(200) = null, 
@database varchar(200)= null, @owner varchar(200) = null ) AS BEGIN
	SET NOCOUNT ON;

declare @id uniqueidentifier
	--Create unique job name if the name is not specified
	if @jobname is null set @jobname= 'async'
	set @jobname = @jobname + '_' + convert(varchar(64),NEWID())

if @owner is null set @owner = 'sa'

--Create a new job, get job ID
	execute msdb..sp_add_job @jobname, @owner_login_name=@owner, @job_id=@id OUTPUT

--Specify a job server for the job
	execute msdb..sp_add_jobserver @job_id=@id

--Specify a first step of the job - the SQL command
	--(@on_success_action = 3 ... Go to next step)
	execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step1', @command = @sql, 
		@database_name = @database, @on_success_action = 3

--Specify next step of the job - delete the job
	declare @deletecommand varchar(200)
	set @deletecommand = 'execute msdb..sp_delete_job @job_name='''+@jobname+''''
	execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step2', @command = @deletecommand

--Start the job
	execute msdb..sp_start_job @job_id=@id

END
go
execute sp_async_execute 'execute sp..sp_AppendToFile ''j:\test.txt'', ''somedata'''
execute sp_async_execute 'execute sp..sp_CacheDelete ''webdata\usd\*'''

Copyright and use this code

The source code on this page and other samples at https://www.motobit.com/tips/ are a free code, you can use it as you want: copy it, modify it, use it in your products, ...

If you use this code, please:

1. Leave the author note in the source.

2. Link this sample from you page.

<A
 Href="https://www.motobit.com/tips/detpg_async-execute-sql/"
 Title="Stored procedure executing  sql command
	as a separated asynchronous process."
>Asynchronous Execution of T-SQL command, MSSQL</A>

Asynchronous Execution of T-SQL command, MSSQLMOTOBIT.COM

See also

Copyright and use this code