Hello! I’m trying to create function that returns BIT value. This function returns the answer if the JOB execution still running. But I get an error:Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function. Is this possible to use EXECUTION of stored procedure in Scalar function? Or, maybe, there are mistakes in my code. P.S. I'm a SQL newbie, so, please, be nice to me My code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET NOCOUNT ON GO CREATE FUNCTION [fn_JobStatusCheck_GAS_log1] (@job_owner sysname) RETURNS BIT AS BEGIN 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, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) SET @job_owner = SUSER_SNAME() INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner DECLARE @IsJobRunning BIT SELECT @IsJobRunning = x.running FROM @xp_results x INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id WHERE sj.name = 'MyJobName' RETURN @IsJobRunning END GO
First , welcome to Forums It is inapplicable to use stored procedure within Functions but only Function and stored procedure , thereby what you can do by that case is to translate the function to a SP using output function like that one below “fn_JobStatusCheck_GAS_log1_SP” to be able to get an equivalent meaning for your function fn_JobStatusCheck_GAS_log1 --Create new SP CREATEprocedure[fn_JobStatusCheck_GAS_log1_SP] @job_owner sysname, @IsJobRunning bitoutput AS BEGIN DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIERNOTNULL, last_run_date INTNOTNULL, last_run_time INTNOTNULL, next_run_date INTNOTNULL, next_run_time INTNOTNULL, next_run_schedule_id INTNOTNULL, requested_to_run INTNOTNULL, request_source INTNOTNULL, request_source_id sysnameCOLLATE database_default NULL, running INTNOTNULL, current_step INTNOTNULL, current_retry_attempt INTNOTNULL, job_state INTNOTNULL) SET @job_owner =SUSER_SNAME() INSERTINTO @xp_results EXECUTEmaster.dbo.xp_sqlagent_enum_jobs1, @job_owner SELECT @IsJobRunning = x.running FROM @xp_results x INNERJOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id WHERE sj.name ='MyJobName' RETURN @IsJobRunning END --Exec SP DECLARE @IsJobRunning BIT EXEC fn_JobStatusCheck_GAS_log1_SP @JOB_OWNER ='SA',@IsJobRunning=@IsJobRunning OUTPUT SELECT @IsJobRunning Kindly try it and let me know your feedback
