运行存储过程并从VBA返回值 [英] run stored procedure and return values from VBA

问题描述

我想将参数传递到SQL Server 2008中的存储过程，我想将结果存储在一个 ADODB.Recordset

目前我有：

  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 =提供商= SQLOLEDB.1; &安培; _ 
Data Source =（local）; Initial Catalog = NorthWind; &安培; _ 
集成安全= SSPI
 
设置conn =新建ADODB.Connection 
 conn.Open strConn 
 
设置cmd =新建ADODB.Command 
 cmd.CommandText =procOrderUpdate
 cmd.CommandType = adCmdStoredProc 
 cmd.ActiveConnection = conn 
 
设置prm = cmd.CreateParameter（OrderID，adInteger，adParamInput ）
 cmd.Parameters.Append prm 
 cmd.Parameters（OrderID）。Value = 1 
 
设置prm = cmd.CreateParameter（OrderDate，adDate，adParamInput） 
 cmd.Parameters.Append prm 
 cmd.Parameters（OrderDate）。Value =1/1/2007
 
设置prm = cmd.CreateParameter（ShipVia ，adInteger，adParamInput）
 cmd.Parameters.Append prm 
 cmd.Parameters（ShipVia）。Value = 2 
设置prm = cmd.CreateParameter（Freight，adCurrency，adParamInput） 
 cmd.Parameters.Append prm 
 cmd.Parameters（Freight）。V alue =10.5
 
'执行存储过程
 cmd.Execute 
 
'关闭连接
 conn.Close 
 End Sub 
  

问题是如何将它存储在记录集中而不是执行它？

解决方案

我相信你需要的是这个

  Dim Rst As ADODB.Recordset 
  

这个

 设置Rst = cmd.Execute 
  

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

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?

解决方案

I believe all you need is this

Dim Rst As ADODB.Recordset

and this

Set Rst = cmd.Execute

这篇关于运行存储过程并从VBA返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！