从VBA中运行存储过程和返回值。

I would like to pass parameters into a stored procedure in SQL Server 2008 and I would like to store the results in a ADODB.Recordset

我希望将参数传递到SQL Server 2008中的存储过程中，并将结果存储在ADODB.Recordset中

Currently I have this:

目前我有这个:

Public Sub UpdateWithStoredProcedure()
        Dim cmd As New ADODB.Command
        Dim conn As ADODB.Connection
        Dim prm As ADODB.Parameter
        Dim strConn As String
        Dim strSQL As String

        strConn = "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "Integrated Security=SSPI"

        Set conn = New ADODB.Connection
        conn.Open strConn

        Set cmd = New ADODB.Command
        cmd.CommandText = "procOrderUpdate"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = conn

        Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderID").Value = 1

        Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderDate").Value = "1/1/2007"

        Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("ShipVia").Value = 2
        Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("Freight").Value = "10.5"

        'Execute the Stored Procedure
        cmd.Execute

        'Close the connection
        conn.Close
    End Sub

The question is how do I store this in a recordset instead of just executing it?

问题是我如何将它存储在一个记录集中而不是仅仅执行它?

1 个解决方案

#1

I believe all you need is this

我相信你所需要的就是这个

Dim Rst As ADODB.Recordset

and this

这

Set Rst = cmd.Execute