mssql get jobstate by name

DECLARE @job_state INT     
Exec msdb..sp_get_jobstate_byname 'job_name',@job_state out   
select @job_state  
  
----------------  
use msdb  
go  
  
create procedure dbo.sp_get_jobstate_byname  
    @job_name             varchar(100)   
    ,@job_state         int out    
AS    
BEGIN    
    DECLARE @is_sysadmin INT    
    DECLARE @job_owner   sysname     
    DECLARE @job_id  UNIQUEIDENTIFIER  
    
    SET NOCOUNT ON    
    
    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)    
    
    select  @Job_ID=Job_ID from msdb.dbo.sysjobs where name = @job_name  
      
    -- Need a job_id    
    if (@Job_ID IS NULL)    
    BEGIN     
        RAISERROR(14262, -1, -1, '@job_name', @job_name)    
        RETURN(1) -- Failure    
    END    
    
    -- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)    
    SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)    
    SELECT @job_owner = suser_sname(suser_sid())    
    INSERT INTO #xp_results    
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner    
    
    -- Select the job state of the job in question    
    SELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id    
        
    -- All done    
    DROP TABLE #xp_results    
    RETURN(0)    -- Success    
END