GO

CREATE PROCEDURE [dbo].[sp_dba_GetSqlJobExecutionStatus](@job_name sysname, @select_data int =0, @execution_status int = NULL OUTPUT)

AS

SET NOCOUNT ON

/*

Is the execution status for the jobs.

Value Description

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

*/

DECLARE @job_id uniqueidentifier,

@is_sysadmin int,

@job_owner sysname

SELECT @job_id = jv.job_id,

@job_owner = sp.name
FROM msdb.dbo.sysjobs_view jv
INNER JOIN sys.server_principals sp
ON jv.owner_sid = sp.sid
WHERE jv.name = @job_name

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

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, -- 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
)

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

SELECT @execution_status = job_state
FROM #xp_results

DROP TABLE #xp_results

IF @select_data =1
SELECT @job_name AS job_name,

@execution_status AS execution_status

SET NOCOUNT OFF

GO

EXEC sp_MS_marksystemobject 'sp_dba_GetSqlJobExecutionStatus'