tim laqua dot com Thoughts and Code from Tim Laqua

	IF  NOT EXISTS (SELECT 1
		FROM AppJobDependency a WITH(NOLOCK)
		CROSS APPLY [dbo].[Utility_GetAgentJobStatus_F01](a.[JobName]) c
		WHERE [JobStatus] = 1)
	BEGIN
		-- Code goes here
	END

/*************************************************************************************
***
*** Procedure:  		[Utility_GetAgentJobStatus_F01]
*** Purpose:		    Get the current status of given agent job
***				
***			
*** Author:		      tl
*** Date Created:	  2009-03-03
*** 
*** Revision History
*** Date		Author			Description
*** 2009-03-03	tl				Created
*************************************************************************************/
CREATE FUNCTION [dbo].[Utility_GetAgentJobStatus_F01] 
(
	@JobName varchar(255)
)
RETURNS @JobStatus TABLE ([JobStatus] BIT)
AS
BEGIN
 
	WITH JobActivity AS
	(
	SELECT 
		 a.start_execution_date
		,a.stop_execution_date
		,ROW_NUMBER() OVER (ORDER BY session_id DESC) AS [RowNumber]
	FROM
		msdb.dbo.sysjobactivity a WITH(NOLOCK)
		INNER JOIN msdb.dbo.sysjobs b WITH(NOLOCK)
		   ON 
			a.[job_id] = b.[job_id]
			AND b.[name] = @JobName
	)
	INSERT INTO @JobStatus
	SELECT COUNT(1)
	FROM JobActivity
	WHERE
		start_execution_date IS NOT NULL
		AND stop_execution_date IS NULL
		AND RowNumber = 1
 
	RETURN
END