USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[SpGetJobStatus]    Script Date: 11/20/2014 9:25:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SpGetJobStatus] (
	@Jobname SYSNAME
	,@state INT OUTPUT
	)
AS
DECLARE @job_owner SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @RowCount INT
DECLARE @Jobs AS TABLE (
	RowCounter INT IDENTITY(1, 1)
	,job_id UNIQUEIDENTIFIER
	,job_owner SYSNAME
	,Jobname SYSNAME
	)

INSERT INTO @Jobs (
	job_id
	,job_owner
	,Jobname
	)
SELECT job_id
	,sl.NAME
	,sj.NAME
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
WHERE sj.NAME = @Jobname

--Or @Jobname IS NULL
SET @RowCount = @@ROWCOUNT

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
	)

WHILE @ROWCOUNT > 0
BEGIN
	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
		,@job_owner
		,@job_id

	SET @RowCount = @RowCount - 1
END

SET @state = (
		SELECT job_state
		FROM @xp_results results
		INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
		WHERE sj.NAME = @Jobname
		)

SELECT sj.NAME
	,job_state
	,CASE job_state
		WHEN 1
			THEN 'Executing'
		WHEN 2
			THEN 'Waiting for thread'
		WHEN 3
			THEN 'Between retries'
		WHEN 4
			THEN 'Idle'
		WHEN 5
			THEN 'Suspended'
		WHEN 6
			THEN '<unknown>'
		WHEN 7
			THEN 'Performing completion actions'
		ELSE ''
		END job_state_description
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
WHERE sj.NAME = @Jobname

RETURN @state

DECLARE @jobstate INT 

EXEC [dbo].[SpGetJobStatus] 'job1',@state = @jobstate OUTPUT

SELECT @jobstate AS jobstate 

DECLARE @jobstate INT

EXEC [dbo].[SpGetJobStatus] 'job1'
	,@state = @jobstate OUTPUT

WHILE (@jobstate != 4)
BEGIN
	EXEC [dbo].[SpGetJobStatus] 'job1'
		,@state = @jobstate OUTPUT

	WAITFOR DELAY '00:00:05'
END

SELECT @jobstate
	-- Do something job has finished 
	-- job1 is idle start another job