我的vba代码设计为使用输出参数从存储过程（在SQL中）返回值，但是我无法获取要返回的值

我想到了。对于任何好奇的人，只要您希望从vba的SQL中从数据库返回单个值，就不要使用记录集。（尽管我猜你可以），原因是Recordsets被设计为以表的形式返回数据-这给我造成了很多问题。

要返回单个值，您要做的就是在vba中执行SQL命令，然后将返回变量设置为等于存储过程中输出参数的值。如果那没有意义，我将所有工作代码从vba和存储过程中放到下面。

TEST SUB-我用它来测试一个来自excel的可能输入的示例。

Private Sub TestSub()

SQL_StoredProcedure "Stainless Steel", "DENSITY", "WIRE_TYPE", "WIRE_INDEX"

End Sub

VBA模块

Option Explicit

Private output As String

' Parameters are passed in that will be used to create a SQL statement
' for a stored procedure.
Public Sub SQL_StoredProcedure(ByVal sql_ui As String, _
                                ByVal sql_pci As String, _
                                ByVal sql_sci As String, _
                                ByVal sql_dtn As String)

    On Error GoTo RunTimeError

    Dim sqlconxn As ADODB.connection ' Connection between vba and SQL
    Dim sqlcmd As ADODB.Command ' Operates as a command between SQL and vba
    Dim sqlfld As ADODB.field ' Used to refer to the records or fields in SQL

    Dim output As String ' The output retrieved from the SQL stored procedure
    Dim conxnString As String ' Connection string is used to make the connection between vba and SSMS

    Application.ScreenUpdating = False

    ' String used to establish a connection to the database
    conxnString = '{your connection string}'

    ' Every time the SQL_StoredProcedure function is called a new instance of the
    ' connection, command, and recordset are made and then immediately closed once the function finishes
    Set sqlconxn = New ADODB.connection
    Set sqlcmd = New ADODB.Command

    sqlconxn.ConnectionTimeout = 30
    sqlconxn.Open conxnString ' makes the connection between SQL

    MsgBox "Connection 1 state: " & GetState(sqlconxn.state) ' Checks the status of the connection

    sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
    sqlcmd.CommandText = "GET_ADDITIVE_DATA" ' name of the stored procedure
    sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection

    ' Parameters that need to be called and defined whenever the stored procedure is called
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@user_index", adVarChar, adParamInput, 255, sql_ui)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@primary_col_index", adVarChar, adParamInput, 255, sql_pci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@secondary_col_index", adVarChar, adParamInput, 255, sql_sci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255, sql_dtn)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@record_value", adVarChar, adParamOutput, 255)

    ' Executes the sql command with all parameters already passed in
    sqlcmd.Execute

    ' output string is set equal to the output parameter of the stored procedure
    output = sqlcmd.Parameters("@record_value").Value

    Debug.Print "OUTPUT: " & output ' prints whatever was returned from the SP

    sqlconxn.Close ' Closes the sqlconxn

    Exit Sub

RunTimeError: ' Reportd any errors that might occur in the system and

    Dim strError As String
    strError = "ERROR: " & Err.Number & vbCrLf & Err.Description
    MsgBox strError
    Debug.Print strError

Exit Sub

End Sub

检查状态功能-这只是连接确认

Private Function GetState(state As Integer) As String

    Select Case state
        Case adStateClosed
            GetState = "Closed"
        Case adStateOpen
            GetState = "Open"

    End Select

End Function

SQL存储过程

USE AFCD
GO

ALTER PROCEDURE dbo.GET_ADDITIVE_DATA (@user_index nvarchar(max),
                                       @primary_col_index nvarchar(max),
                                       @secondary_col_index nvarchar(max), 
                                       @data_table_name nvarchar(max),
                                       @record_value nvarchar(max) output
                                       ) AS
BEGIN

    DECLARE @output_value nvarchar(max)
    DECLARE @query nvarchar(max)

    SET @query = 'SELECT @record_value = ' + @primary_col_index  + 
                 ' FROM ' + @data_table_name +
                 ' WHERE ' + @secondary_col_index + ' = ''' + @user_index + ''''

    EXEC sp_executesql @query, 
                       N'@record_value nvarchar(max) output', 
                       @record_value = @record_value output

END

输出值

OUTPUT: 0.284