use master
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_IsJobIdle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_IsJobIdle]
GO


CREATE PROC sp_IsJobIdle @job_name SYSNAME,@idle char(1) OUTPUT
AS
/*
Check if a certain job is idle

Returns @idle char(1)

Returns 'Y' if job is Idle 
Returns 'N' if job is not Idle

Author		Date
J Smith 	08/05/2002

*/
BEGIN
SET NOCOUNT ON

  DECLARE @job_id          UNIQUEIDENTIFIER
  DECLARE @is_idle         INT

--Check name exists

  IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs (nolock) where name=@job_name)
  BEGIN
	RAISERROR('The job name specified does not exist',16,1)
	RETURN 1
  END

--Temp table to store results of xp_sqlagent_enum_jobs
  CREATE TABLE #xp_results (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, 
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, 
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

--Get the Job ID
  EXECUTE  msdb.dbo.sp_verify_job_identifiers 	'@job_name',
						'@job_id',
                                               	 @job_name OUTPUT,
                                               	 @job_id   OUTPUT
--Get Job Details
  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa', @job_id

--Get Job Status
/*
        0 Returns only those jobs that are not idle or suspended.  
        1 Executing. 
        2 Waiting for thread. 
        3 Between retries. 
        4 Idle. 
        5 Suspended. 
        7 Performing completion actions. 
*/
  SELECT @is_idle=job_state FROM #xp_results
  DROP TABLE #xp_results

--If Not Idle return 'N'
  IF @is_idle<>4 
  BEGIN
	SELECT @idle='N'
	RETURN
  END
  
--If Idle Return 'Y'
  SELECT @idle='Y'
  RETURN 
END
GO

declare @idle char(1)
exec sp_IsJobIdle 'SAP_dataload',@idle OUTPUT

If @idle='N' raiserror('Job running',16,1)
else 
begin
      -- DO STUFF
end