VBA调用SQL Server存储过程第二次执行输出参数返回NULL问题

核心问题原因 #

1. ADODB调用存储过程时未指定adExecuteNoRecords参数：该存储过程没有返回查询结果集，默认执行时ADODB会等待处理隐式生成的空记录集，导致输出参数无法正常填充，第二次执行时该问题更易触发。
2. 存储过程查询作业运行状态的逻辑错误：原有代码用作业名称@vJobName匹配sysjobhistory表的step_name字段，而step_name是作业步骤的名称，不是作业本身的名称，绝大多数场景下匹配不到记录，导致@vJobStatus被赋值为NULL。
3. 输出参数无默认初始值：当查询不到匹配的作业历史记录时，输出参数会保持未赋值状态，返回NULL。

修复步骤 #

VBA端修复 #

两次调用cmd.Execute时都添加adExecuteNoRecords参数，告知ADODB无需处理返回结果集，直接填充输出参数；第二次执行前可手动重置输出参数值，避免旧值干扰。
修复后的关键代码：

Public pm As ADODB.Parameter
Public cnnStr, StrQuery, vJobName As String
Public RunStatus, vJobStatus As Integer
Global Flag_Chkbox As Integer


Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Run_SQL_Job"
cmd.CommandTimeout = 120
Set pm = New ADODB.Parameter

Set pm = cmd.CreateParameter("@vJobName", adVarChar, adParamInput, 50, "TestJob")
cmd.Parameters.Append pm
      
Set pm = cmd.CreateParameter("@vJobStatus", adInteger, adParamOutput, , vJobStatus)
cmd.Parameters.Append pm
    
' 加adExecuteNoRecords参数
cmd.Execute , , adExecuteNoRecords
vJobStatus = cmd.Parameters("@vJobStatus").Value
       
If vJobStatus <> 1 Then
    cnn.Close
    Application.Interactive = True
    Application.StatusBar = ""
    Exit Sub
Else
    cmd.Parameters("@vJobName").Value = "TestJob1"
    ' 重置输出参数避免旧值干扰
    cmd.Parameters("@vJobStatus").Value = 0
    ' 第二次执行也加adExecuteNoRecords参数
    cmd.Execute , , adExecuteNoRecords
    vJobStatus = cmd.Parameters("@vJobStatus").Value
End If

存储过程端修复 #

修正作业历史查询逻辑，先用作业名获取作业ID，再用作业ID查询运行历史；添加SET NOCOUNT ON减少额外干扰；给输出参数设置初始默认值，避免查询不到结果时返回NULL。
修复后的完整代码：

CREATE PROCEDURE [dbo].[Run_SQL_Job] 
      @vJobName VARCHAR(50)  
     ,@vJobStatus INTEGER  OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;
    -- 初始化输出参数默认值，避免返回NULL
    SET @vJobStatus = -1;
    DECLARE @vJobId UNIQUEIDENTIFIER;
    -- 根据作业名获取对应作业ID
    SELECT @vJobId = job_id FROM msdb.dbo.sysjobs WHERE name = @vJobName;

    DECLARE @vRunStatus as INT
    DeclarE @vJobStats as INT

    WAITFOR DELAY '00:00:5';
    SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])

    WHILE @vRunStatus >= 1
     BEGIN
       SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
     END

    -- 校验作业存在才执行启动逻辑
    IF @vRunStatus = 0 AND @vJobId IS NOT NULL
      BEGIN
        EXEC msdb.dbo.sp_start_job @job_name = @vJobname 
        WAITFOR DELAY '00:00:10';
      END
      
    SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
        
    WHILE @vRunStatus >= 1
     BEGIN
       SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
     END

    -- 修正查询逻辑：用作业ID筛选，取作业整体最新运行结果
    SELECT TOP 1 @vJobStatus = run_status 
    FROM msdb.dbo.sysjobhistory
    WHERE job_id = @vJobId 
      AND step_id = 0 -- step_id=0代表作业整体的运行结果，而非单个步骤
    ORDER BY instance_id DESC;
END
GO

内容的提问来源于stack exchange，提问作者Sree。