SQL Server stored procedure syntax to return retval from execute passing job name to sp_stop_job

Question

I have a poor SQL Server experience but, having to maintain an old (very old) SQL Server instance, I'm currently trying to write a complex stored procedure but it's failing (due to syntax) when I try, eg, to stop a job.

So, I am now struggling to find a working syntax to:

• execute msdb.sp_stop.job
• passing it a job name which I've stored into a @job_name variable
• returning a (declared) @retval variable to later understand if the job actually stopped or not.

I am trying some syntax variations (all failing for some reason), and I guess my poor experience needs help, hints. I tried to spot from some other default procedures, too (like sp_help_job)

Here is my last test try:

GO
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE my_test_stop_job
    -- Individual job parameters
    @job_name                   sysname          = NULL  -- If provided should NOT also provide job_id

AS

BEGIN
    DECLARE @retval          INT

    -- Remove any leading/trailing spaces from parameters (except @owner_login_name)
    SELECT @job_name         = LTRIM(RTRIM(@job_name))

    -- Turn [nullable] empty string parameters into NULLs
    IF (@job_name         = N'') SELECT @job_name = NULL

    IF (@job_name IS NOT NULL)      
        BEGIN       
            EXECUTE ('@retval = EXECUTE msdb.dbo.sp_stop_job @job_name = ''' + @job_name + '''')

                    IF (@retval <> 0)
                            RETURN(1) -- Failure                        
                        ELSE                
                            RETURN(0) -- status OK

        END

END

GO

which, launched using

EXEC mydb.dbo.my_test_stop_job @job_name = 'jobname2bestopped'

fails giving:

must declare the variable '@retval'

How can this be done? Thanks

Answer 1

There is no need for dynamic-SQL:

CREATE PROCEDURE my_test_stop_job
    -- Individual job parameters
    @job_name                   sysname          = NULL  
AS
BEGIN
    DECLARE @retval          INT;

    -- Remove any leading/trailing spaces from parameters 
    SELECT @job_name         = LTRIM(RTRIM(@job_name));

    -- Turn [nullable] empty string parameters into NULLs
    IF (@job_name         = N'') SELECT @job_name = NULL;

    IF (@job_name IS NOT NULL)      
    BEGIN       
        EXECUTE @retval =  msdb.dbo.sp_stop_job @job_name = @job_name;

        IF (@retval <> 0)
            RETURN(1); -- Failure                        
        ELSE                
            RETURN(0); -- status OK
    END
END   
GO

Also I suggest to THROW exception when job name is NULL otherwise your stored procedure will exit with code 0.

---

If from some reason you need dynamic-SQL, you could use EXEC dbo.sp_executesql with OUTPUT parameter.