Check if a SQL Agent Job is Running

CheckIfSQLAgentJobIsRunning

SET NOCOUNT ON
DECLARE @isRunning BIT
DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)
 
BEGIN TRY
      SET @isRunning = 0;
     
      INSERT INTO @xp_results
      EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
 
      -- next line is for debugging purposes only
      -- SELECT * FROM @xp_results
     
      SELECT @isRunning = xpr.running                
      FROM @xp_results xpr
            INNER JOIN dbo.sysjobs_view sjv ON xpr.job_id = sjv.job_id
      WHERE sjv.name='SQLJobName'
END TRY
BEGIN CATCH
END CATCH
 
SELECT ISNULL(@isRunning,0) AS [IsRunning]

Thanks to http://weblogs.asp.net/johnbilliris/archive/2009/10/13/how-to-determine-if-a-sql-job-is-running.aspx to the intial code