Execute T-SQL Asyncronously

/*
 *****  This entire comment portion is not necessarily part of SP, just notes on subject *****

  The purpose of this SP is to provide a method in T-SQL to launch another T-SQL thread without having
to wait for its completion, or "local" concern for its correct processing. A good example of this is
when your T-SQL code has finished a certain bit of processing, and now the database is in a "state" 
where performing a backup is a good idea, but your current T-SQL code still has some time consuming
work to perform. This SP will allow you to go ahead and "launch" the backup and return immediately
to the current T-SQL code. Another area where this can be used is when a user wants to start a SQL
Server process, but you want the user interface to get back control right away, without having to 
wait for the SQL process to complete (this also helps with SQL processes that return timeouts when
called from user interfaces because the process takes longer than the [sometimes difficult to deal 
with] user interface timeout configurations.
  The basic principal within this SP is to create a SQL Server Agent Job that contains the T-SQL
to perform the desired process. Jobs have a few components. 1. The Job itself is maily just a 
named "container" for the Job Step(s) and Job Schedule(s). 2. One or more Job Steps that contain
the actual T-SQL code, and various properties about what happens when the Step is executed. 
3. Zero or more Job Schedules (none used in this SP) to be able to configure Jobs to run at a certain 
time or interval. The built in SPs that deal with these components are sp_add_job, sp_add_jobstep, 
and sp_add_jobschedule, all of which reside in the MSDB database. I encourage you to look at these in 
BOL, if just to become familiar with the possibilities that Jobs provide. In Enterprise Manager, access
to these components are at the [Servername]-Management-SQL Server Agent-Jobs branch. Jobs may affect
most all the (stock) tables in the MSDB database, but the major components are in SysJobs, SysJobSteps,
and SysJobSchedules tables. Note: The Job Step @DatabaseName parameter determines the "current" database
to execute the Step's T-SQL code. If I want to execute the same bit of T-SQL for multiple databases, I
simply add more Job Steps to the same Job. IMPORTANT - SQL Server Agent service must be running to have 
Jobs execute.
  Since I'm not a SQL Server Admin guru, and in my implementaions of this it has not been an issue,
the security context under which the Job Steps will execute may have an impact on whether or not your
T-SQL inside the Job Steps can execute properly. It depends on the security context of the T-SQL code
that calls this SP along with context under which SQL Server Service and/or the SQL Server Agent Service
is being run. See BOL - sp_start_job for more information on this subject.
  This SP is a stripped down version of the SP I actually use in production. I have various SPs that
will create and execute Jobs. Some return immediately, while others leverage other aspects of executing
T-SQL code within a Job as opposed to "inline". For example, in the SP code below I test whether the SQL
Agent is running, and if not, simply output an error message. This could easily be modified to go ahead
and execute the passed T-SQL (and wait for its completion) if the Agent is not running. In my production
SP, I "log" calls to this code and test for execution errors etc. Interestingly, a Job will actually
produce records in the MSDB..SysJobHistory table that contain the actual T-SQL PRINT statement outputs,
or SQL error messages you generally see in the text output window wihtin Query Analyzer. I use this
in some data driven debugging to be able to have the actual SQL error messages available as opposed to
inline code dealing with just the @@Error codes. This data can also be output to O/S text files. Only
one minor problem, they are unicode.
  When calling this SP, your expectation should be a return to the calling code within a fraction of a
second. I would not recommnend calling this many times within a loop or something. It would not only be
a fairly slow loop, but, all of a sudden SQL Server will have many, many threads (SPIDS) running at the
same time. This may be a great testing methodology in some instances, but should not be done in a 
production environment. On a "quiet" SQL Sever, the Job this SP creates should start executing within a
fraction of a second. If many Jobs are aleady executing, it may take seconds or longer for Jobs to start.
I have issued a hundred or so calls in a loop for testing, and have observed within Enterprise Manager--Jobs
how the jobs start execute and end. I have not seen any affect on the performance of T-SQL code while 
being executed from within a job.
  This SP creates uniquely named jobs. If the passed T-SQL executes without an error, the Job will delete 
itself. This also means, if there is an error, the Job this SP creates will remain. It must then be either 
manually deleted, or executed successfully to be removed. If the Job does fail, view the Job History from 
within Enterprise Manager (check the "Show Step Details").

  If you notice, the 1st part of the SP has an IF with a bunch of "constant" text. I use this as a way to
self-document my SPs. If the SP requires a parameter, and it is not supplied, I treat the SP call as if
someone had typed "SyExecSQLASyncSP /?" or something, and PRINT out the documentation of the SP. This has
become a practice of mine and is very usefull to me (since I can't remember squat anymore). Often, in
Query Analyzer I see a call to an SP, I just simply double-click on it to select it, and hit F5 to run it.
It then outputs it's own documentation and refreshes my memory of it's usage or purpose.

I'll mention it again. I highly recommend looking up the information in BOL about the commands used in this SP.

 *****  End Of - This entire comment portion is not necessarily part of SP, just notes on subject *****
*/Create Procedure SyExecSQLASyncSP
@Command VarChar(3200) = NULL
AS

IF @Command IS NULL BEGIN -- Self Doc Section
PRINT 'Executes passed T-SQL batch ASyncronously by creating and then running a SQL Server Job.

EXEC SyExecSQLASyncSP [@Command = ''T-SQL script''] 

Parameter:
@CommandUp to 3200 character string of T-SQL code

Example Call:
EXEC SyExecSQLASyncSP ''EXEC SomeSP''

 or to execute something in 5 minutes, but return control right away
EXEC SyExecSQLASyncSP ''WAITFOR DELAY ''''00:05:00'''' EXEC SomeSP''
'
RETURN
END -- End Self Doc Section

DECLARE @JobID UNIQUEIDENTIFIER, 
@JName VarChar(128), 
@JDesc VarChar(512), 
@Now VarChar(30), 
@sDBName VarChar(128),
@Note VarChar(8000)

-- Check IF Agent is running
IF (SELECT count(*) FROM Master.dbo.SysProcesses WHERE Program_Name = 'SQLAgent - Generic Refresher') = 0 BEGIN
SET @Note = 'Errors occured in ASync request. SQL Server Agent is NOT running.' + Char(13) + Char(10) + 
'Batch command request:' + @Command
RAISERROR (@Note, 0, 1) With NoWait
RETURN
END

SET @sDBName = DB_NAME(DB_ID())

-- Create a Job to run passed SQL batch
SET @Now = (SELECT convert(VarChar,getdate()) + ':' + convert(VarChar,DATEPART(s, getdate())) + ':' + convert(VarChar,DATEPART(ms, getdate())) )
SET @JName = 'Temp Job (ASync DB:' + @sDBName + ') ' + @Now 
SET @JDesc = 'Temp Job to run command ASyncronously on database:' + @sDBName
EXEC msdb..sp_add_job 
@job_name = @JName, 
@enabled = 1, 
@description = @JDesc, 
@delete_level = 1,-- delete job on completion
@job_id = @JobID OUTPUT

-- Add target server to job
EXEC msdb..sp_add_jobserver @job_id = @JobID, @server_name = @@SERVERNAME

-- Create  step 1, Actual command
EXEC msdb..sp_add_jobstep 
@job_id = @JobID, 
@step_name = 'Run T-SQL ASyncronously', 
@subsystem = 'TSQL', 
@command = @Command,
@database_name = @sDBName

-- Execute the Job
EXEC msdb..sp_start_job @job_name = @JName, @error_flag = Null, @server_name  = Null, @step_name = Null, @output_flag = 0