168. A case where a stored procedure is invoked asynchronously using a job

CREATE PROC sp_job_LoadPorc @command nvarchar(4000), -- the stored procedure called asynchronously or the transact-SQL statement to execute @databasename sysname=NULL, @jobdelay int=5 @jobdelay int=5 The value must be greater than or equal to 5 @jobid uniqueidentifier OUTPUT -- defined jobid AS -- jobname, job execution time DECLARE @jobname sysname,@time int SELECT @ jobname = N + N '_' 'temporary assignments' + LEFT (HOST_NAME (), 40) + N' _ '+ CONVERT (char (19), GETDATE (), 120) + N' _ '+ CAST (NEWID () as varchar(36)), @jobdelay=CASE WHEN ISNULL(@jobdelay,0)<5 THEN 5 ELSE @jobdelay+1 END, @time=REPLACE(CONVERT(char(8), DATEADD(Second,@jobdelay,GETDATE()), 108),':',') -- DatabaseName IF DB_ID(@databasename) IS NULL SET @databasename =DB_NAME() -- IF EXISTS, delete IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname) EXEC msdb.. Sp_add_job @job_name=@jobname, @delete_level =3, Sp_add_jobstep @job_id = @jobid OUTPUT EXEC msdb.dbo.sp_add_jobstep @job_id = @jobid OUTPUT @subsystem = 'TSQL', @subsystem = 'TSQL', @database_name= @databasename, @command = @command -- create schedule EXEC MSDB.. Sp_add_jobschedule @job_id = @jobid, @name = N' Asynchronously invoke stored procedures or execute transact-SQL statements ', @freq_type=1, Active_start_time = @time -- Add target server DECLARE @servername sysname SET @servername=CONVERT(nvarchar(128),SERVERPROPERTY(N'ServerName')) EXEC msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = @servernameCopy the code