VBA调用SQL存储过程无法获取输出参数值问题排查

Let's break down the issues with your code—there's a critical typo in your SQL stored procedure that's causing the NULL output, plus a few best practices to make your VBA more reliable:

1. Critical Typo in the SQL Stored Procedure #

Your output parameter is misspelled as @ouput (missing a 't') instead of @output. Because of this, when you assign SELECT @ouput = 1 or 0, you're actually setting a non-existent local variable (not the official output parameter), so the real output parameter remains uninitialized (NULL).

Fix the stored procedure first:

CREATE PROCEDURE BrandewynTest 
    @value VARCHAR(50),
    @output INT OUTPUT -- Corrected spelling
AS
BEGIN
    IF @value = 'value'
        SELECT @output = 1
    ELSE
        SELECT @output = 0
END

2. VBA Improvements (Avoid Reliance on Parameters.Refresh) #

While Parameters.Refresh can work, it's often unreliable (depends on driver support and connection settings) and makes your code harder to debug. Explicitly defining your parameters is a better, more maintainable practice:

Option Explicit

Public Sub BrandewynTest()
    Dim cmd As ADODB.Command
    Dim inputParam As ADODB.Parameter
    Dim outputParam As ADODB.Parameter
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = g_objConnection
        .CommandType = adCmdStoredProc
        .CommandText = "BrandewynTest"
        
        ' Define input parameter explicitly
        Set inputParam = .CreateParameter("@value", adVarChar, adParamInput, 50, "value")
        .Parameters.Append inputParam
        
        ' Define output parameter with direction clearly specified
        Set outputParam = .CreateParameter("@output", adInteger, adParamOutput)
        .Parameters.Append outputParam
        
        ' Execute (no recordset needed here since your proc doesn't return rows)
        .Execute
        
        ' Now you'll get the correct output value
        Debug.Print outputParam.Value
    End With
    
    ' Clean up objects to avoid memory leaks
    Set outputParam = Nothing
    Set inputParam = Nothing
    Set cmd = Nothing
End Sub

Why Your Original VBA Was Failing (Even After Fixing the Typo) #

If you still want to use Parameters.Refresh, be cautious with index references—ADODB uses 0-based indexing in some contexts (though drivers can vary). Referencing the parameter by name instead of index is far safer, e.g.:

Debug.Print .Parameters("@output").Value

That should resolve the NULL output issue entirely!

内容的提问来源于stack exchange，提问作者B.L.Coskey