SQL Server存储过程语法返回RETVAL从执行传递任务名sp_stop_job

Question

我有一个可怜的SQL Server的经验，但是，必须维护一个老（很旧的）SQL Server实例，我正在尝试写一个复杂的存储程序，但是当我尝试时，它会失败（由于语法），例如，停止工作。

所以，我现在正在努力寻找工作的语法：

• 执行msdb.sp_stop.job
• 通过它我已经存储到@job_name变量
• 作业名返回一个（声明的）@retval变量，以便稍后了解该作业是否实际停止。

我在尝试一些语法变体（所有失败的原因），我想我的经验不足，需要帮助，提示。我试图从其他一些默认的程序被发现，太（如sp_help_job）

这是我的最后一次测试尝试：

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 

其中，推出了采用

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

失败，并提供：

must declare the variable '@retval' 

如何才能做到这一点？由于

Answer 1

没有必要对动态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 

此外，我建议THROW异常，当作业名称是NULL否则你的存储过程将使用代码0退出。

---

如果因为某种原因，你需要动态SQL，你可以使用EXEC dbo.sp_executesql与OUTPUT参数。