/* 

IMPORTANT: Creates a job called TestJob1 (and deletes any existing job with that name first)
This job will keep running at intervals of 10 and 23 seconds
This code will keep running
Stopping the code will not delete the job - it will still be running - delete the job after stopping the execution

*/
-- make the job wait for the specified delay and then do some very
-- meaningful thing like selecting the current date
DECLARE @JobSql nvarchar(max),@Delay varchar(12);
SET @Delay = '00:00:05';
SET @JobSql = 'WAITFOR DELAY '+QUOTENAME(@Delay,'''');
SET @JobSql = @JobSql + CHAR(13)+CHAR(10)+'SELECT getdate()';

DECLARE @JobCmd varchar(200) = @JobSql;
-- SET YOUR SERVER NAME HERE!!!
DECLARE @srvname sysname = '<<PUTSERVERNAMEHERE>>';

IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'TestJob1')
BEGIN
	PRINT 'Deleting job';
	EXEC msdb.dbo.sp_delete_job @job_name = 'TestJob1';
END
PRINT 'Creating job';
EXEC msdb.dbo.sp_add_job @job_name = 'TestJob1',@owner_login_name='sa';
EXEC msdb.dbo.sp_add_jobstep @job_name='TestJob1',@step_name = 'Step1',@command=@JobCmd;
EXEC msdb.dbo.sp_add_jobserver @job_name='TestJob1',@server_name=@srvname;
-- 10 second interval schedule
EXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched10Second', 
@enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=10;
-- 23 second interval schedule
EXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched23Second', 
@enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=23;   


DECLARE @jobname sysname,@jobid uniqueidentifier,@joboutcomemsg nvarchar(1024);
SET @jobname = 'TestJob1';
SELECT @jobid = job_id FROM msdb.dbo.sysjobs WITH (NOLOCK) WHERE name = @jobname;
WHILE(1=1)
BEGIN
	WHILE EXISTS(SELECT 1 FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid)
	BEGIN
		IF ((SELECT stop_execution_date FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid )IS NOT NULL)
		BEGIN
			IF (@joboutcomemsg IS NOT NULL)
				SELECT 'Before Break',@joboutcomemsg JobOutcomeMessage;
				
			WAITFOR DELAY '00:00:01'
			BREAK -- job has completed - break out
		END
		ELSE
		BEGIN
			SELECT @joboutcomemsg = last_outcome_message FROM msdb.dbo.sysjobactivity T1 INNER JOIN msdb.dbo.sysjobservers T2 ON
			T1.job_id = T2.job_id
			WHERE T1.job_id = @jobid
			IF (@joboutcomemsg IS NOT NULL)
				SELECT 'During continue',@joboutcomemsg JobOutcomeMessage;

			WAITFOR DELAY '00:00:01'			
			CONTINUE -- continue waiting
		END
	END
END;