SQL: Run SQL Job via Stored Procedure

Here is stored procedure that you can use so that you can run your SQL Job manually via script. It already factored in to check the SQL JOB if its running already or not so that it will just wait that run to finish instead of queuing another one.

CREATE PROC [dbo].[usp_SQLJOB_EXEC] @JobName NVARCHAR(MAX)
AS
	DECLARE @JobID UNIQUEIDENTIFIER;

    -- Get the Job ID
    SELECT @JobID = job_id
    FROM msdb.dbo.sysjobs
    WHERE name = @JobName;

    -- Check if the job exists
    IF @JobID IS NOT NULL
    BEGIN
		DECLARE @IsJobRunning BIT = 1;
		SELECT @IsJobRunning = CASE WHEN EXISTS (
                SELECT 1
                FROM msdb.dbo.sysjobactivity
                WHERE job_id = @JobID
                AND run_requested_date >= DATEADD(dd, -1, GETDATE())
                AND stop_execution_date is null
            ) THEN 1 ELSE 0 END;
        -- Start the job
		IF(@IsJobRunning = 0)
		BEGIN
			EXEC msdb.dbo.sp_start_job @job_name = @JobName;
			SET @IsJobRunning = 1
		END
        

        -- Loop to check the job status
        WHILE @IsJobRunning = 1
        BEGIN
            -- Check if the job is still running
            SELECT @IsJobRunning = CASE WHEN EXISTS (
                SELECT 1
                FROM msdb.dbo.sysjobactivity
                WHERE job_id = @JobID
                AND run_requested_date >= DATEADD(dd, -1, GETDATE())
                AND stop_execution_date is null
            ) THEN 1 ELSE 0 END;

            -- Break the loop if the job is completed
            IF @IsJobRunning = 0
                BREAK;

            -- Wait for a few seconds before checking again
            WAITFOR DELAY '00:00:05'; -- Adjust the delay time as needed
        END;
    END
    ELSE
    BEGIN
        PRINT 'Job not found';
    END

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.