-- Stored Procedure: dbo.IsAgentJobRunnable
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.IsAgentJobRunnable
 @IsRunnable tinyint OUTPUT,
 @job_name sysname, 
 @job_owner sysname = 'sa'
as

create table #job_status
 (job_id uniqueidentifier, 
 last_run_date int,
 last_run_time int,
  next_run_date int,
 next_run_time int,
 next_run_schedule_id int,
        requested_to_run tinyint,
        request_source tinyint,
        request_source_id varchar(50),
        running tinyint,
        current_step int,        
        current_retry_attempt tinyint,
        State tinyint) 

declare @job_id uniqueidentifier

set nocount on

--select @job_id = job_id from msdb.dbo.sysjobs where name = @job_name
--get the union of sysadmin owned jobs and other jobs for this user

select @job_id = job_id
from msdb.dbo.sysjobs
where name = @job_name

if IS_SRVROLEMEMBER ('sysadmin' , @job_owner) = 1
 insert #job_status exec master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
else
 insert #job_status exec master.dbo.xp_sqlagent_enum_jobs 0, @job_owner, @job_id

select @IsRunnable = case when state = 4 then  1 else 0 end
from #job_status 
where job_id = @job_id

/*
select @IsRunnable as [runnable],
 running,
 requested_to_run,
 case 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 7 then 'Performing completion actions'
 end as [execution state]
from #job_status
where job_id = @job_id
*/
return


GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO