CREATE PROCEDURE TriggerJobBasedOnConditionWithSuccessFlag

AS

BEGIN

. DECLARE @Condition INT;

. DECLARE @JobName NVARCHAR(128);

. DECLARE @JobResult INT;

. DECLARE @JobSuccess BIT = 0; – Flag to track job success

.

. – Set the condition here, for example:

. SET @Condition = 1; – Change this value based on your condition

.

. – Set the job name

. SET @JobName = ‘YourJobName’; – Change this to the name of your job

.

. IF @Condition = 1

. BEGIN

. – Check if the job exists

. IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName)

. BEGIN

. – Start the job

. EXEC @JobResult = msdb.dbo.sp_start_job @job_name = @JobName;

.

. – Check job result

. IF @JobResult = 0

. BEGIN

. PRINT ‘Job started successfully.’;

. – Check job status

. WAITFOR DELAY ‘00:00:05'; – Wait for a few seconds for the job to complete

. IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobhistory WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) AND run_status = 1)

. BEGIN

. SET @JobSuccess = 1; – Set flag to indicate job success

. PRINT ‘Job completed successfully.’;

. END

. ELSE

. BEGIN

. PRINT ‘Job failed or still running.’;

. END

. END

. ELSE

. BEGIN

. PRINT ‘Job execution failed.’;

. – Log the failure or perform any other actions as needed

. END

. END

. ELSE

. BEGIN

. PRINT ‘Job does not exist.’;

. END

. END

. ELSE

. BEGIN

. PRINT ‘Condition not met. Job not triggered.’;

. END

.

. – Return job success flag

. SELECT @JobSuccess AS JobSuccess;

END;