use admin
GO
SET QUOTED_IDENTIFIER OFF 
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

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

return


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO