如何在VBA MsgBox中获取SQL Server存储过程的返回值与输出参数

存储过程说明 #

SQL Server中创建的SP_TestParams存储过程，用于计算两个输入参数的和，并根据结果返回对应状态码，具体逻辑：

• 计算输出参数S = L1 + L2
• 返回状态码规则：
  • 若L1或L2为NULL，返回0
  • 若S < 10，返回1
  • 若S >= 10，返回2

存储过程创建语句：

CREATE PROCEDURE [dbo].[SP_TestParams]
    @L1 INT,
    @L2 INT,
    @S INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SET @S = @L1 + @L2;
    
    DECLARE @ret_code INT;
    IF @L1 IS NULL OR @L2 IS NULL
        SET @ret_code = 0;
    ELSE IF @S < 10
        SET @ret_code = 1;
    ELSE
        SET @ret_code = 2;
    
    RETURN @ret_code;
END

存储过程执行示例：

DECLARE @return_value int,
        @S int

EXEC    @return_value = [dbo].[SP_TestParams]
        @L1 = 2,
        @L2 = 2,
        @S = @S OUTPUT

SELECT  @S as N'@S'
SELECT  'Return Value' = @return_value

问题描述 #

使用VBA调用该存储过程时，出现“过程或函数参数过多”的错误，原VBA代码如下：

Sub TriggerProcedure ()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "Driver={SQL Server};Server=MY_DATABASE;Uid=MY_LOGIN;Pwd=MY_PASSWORD;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    cn.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.SP_TestParams"
    cmd.ActiveConnection = cn
    
    Dim L1 As Integer, L2 As Integer, S As Integer, ret_code As Integer
    
    L1 = InputBox("Please enter parameter L1:")
    L2 = InputBox("Please enter parameter L2:")
    
    cmd.Parameters.Append cmd.CreateParameter("@L1", adInteger, adParamInput, , L1)
    cmd.Parameters.Append cmd.CreateParameter("@L2", adInteger, adParamInput, , L2)
    cmd.Parameters.Append cmd.CreateParameter("@S", adInteger, adParamOutput)
    cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue)
    
    cmd.Execute
    S = cmd.Parameters("@S").Value
    return_code = cmd.Parameters("@return_value").Value
    ret_code = cmd.Parameters("@return_value").Value
    
    MsgBox "Parametr S - sum" & L1 & " i " & L2 & " equal to" & S & vbNewLine & "Return code of the procedure is " & ret_code
    
    cn.Close
End Sub

错误原因 #

ADODB.Command对象在调用存储过程时，会自动从SQL Server元数据中加载存储过程的所有参数，包括返回值参数。手动追加@return_value参数会导致参数集合中出现重复项，触发“参数过多”的错误。

修正方案 #

方案1：刷新参数集合（推荐） #

调用cmd.Parameters.Refresh自动加载存储过程的所有参数，再赋值输入参数，无需手动创建返回值参数：

Sub TriggerProcedure()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    ' 替换为你的数据库连接信息
    cn.ConnectionString = "Driver={SQL Server};Server=MY_DATABASE;Uid=MY_LOGIN;Pwd=MY_PASSWORD;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    cn.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.SP_TestParams"
    cmd.ActiveConnection = cn
    
    Dim L1 As Integer, L2 As Integer, S As Integer, ret_code As Integer
    
    L1 = InputBox("Please enter parameter L1:")
    L2 = InputBox("Please enter parameter L2:")
    
    ' 自动加载存储过程的所有参数（返回值、输入、输出）
    cmd.Parameters.Refresh
    
    ' 给输入参数赋值
    cmd.Parameters("@L1").Value = L1
    cmd.Parameters("@L2").Value = L2
    
    ' 执行存储过程
    cmd.Execute
    
    ' 获取输出参数和返回值（返回值参数索引固定为0）
    S = cmd.Parameters("@S").Value
    ret_code = cmd.Parameters(0).Value
    
    ' 修正字符串拼接格式，提升可读性
    MsgBox "Parameter S - sum of " & L1 & " and " & L2 & " equals " & S & vbNewLine & "Procedure return code: " & ret_code
    
    cn.Close
End Sub

方案2：手动添加参数（不创建返回值） #

仅手动添加输入和输出参数，忽略返回值参数的创建（ADODB会自动生成）：

Sub TriggerProcedure()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "Driver={SQL Server};Server=MY_DATABASE;Uid=MY_LOGIN;Pwd=MY_PASSWORD;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    cn.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.SP_TestParams"
    cmd.ActiveConnection = cn
    
    Dim L1 As Integer, L2 As Integer, S As Integer, ret_code As Integer
    
    L1 = InputBox("Please enter parameter L1:")
    L2 = InputBox("Please enter parameter L2:")
    
    ' 仅添加输入和输出参数，返回值由ADODB自动生成
    cmd.Parameters.Append cmd.CreateParameter("@L1", adInteger, adParamInput, , L1)
    cmd.Parameters.Append cmd.CreateParameter("@L2", adInteger, adParamInput, , L2)
    cmd.Parameters.Append cmd.CreateParameter("@S", adInteger, adParamOutput)
    
    cmd.Execute
    
    ' 获取输出参数
    S = cmd.Parameters("@S").Value
    ' 获取自动生成的返回值参数（索引为0）
    ret_code = cmd.Parameters(0).Value
    
    MsgBox "Parameter S - sum of " & L1 & " and " & L2 & " equals " & S & vbNewLine & "Procedure return code: " & ret_code
    
    cn.Close
End Sub

注意事项 #

• 确保已引用ADODB库：在VBA编辑器中，点击工具→引用，勾选Microsoft ActiveX Data Objects x.x Library（推荐选最新版本，如6.1）
• 字符串拼接时注意添加空格，避免显示内容混乱
• 返回值参数的索引固定为0，这是ADODB的默认行为，无需手动命名

内容的提问来源于stack exchange，提问作者Cezary Domański