sql - 从 Excel 运行存储过程

Question

我正在尝试从 Excel 运行存储过程。我知道如何在不使用动态日期的情况下做到这一点，但我需要动态的日期范围。

Sub TestStoredProcedure()

    Dim CServer As String
    Dim CDatabase As String
    Dim CLogon As String
    Dim CPass As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim TStartDate As String
    Dim TEndDate As String

    CServer = "111111"         ' Your server name here
    CDatabase = "111111"    ' Your  database name here
    CLogon = "11111111"     ' your logon here
    CPass = "111111"             ' your  password here

    Dim Cmd1 As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim intTemp As Integer

    Set Cmd1 = New ADODB.Command

    Cmd1.ActiveConnection = cn
    Cmd1.CommandText = "callstatisticsbyQ"
    Cmd1.CommandType = adCmdStoredProc

    Cmd1.Parameters.Refresh
    Cmd1.Parameters(0).Value = Worksheets("Sheet2").Range("A1")
    Cmd1.Parameters(1).Value = Worksheets("Sheet2").Range("A2")
    Cmd1.Parameters(2).Value = Worksheets("Sheet2").Range("A3")

    Set rs = Cmd1.Execute()

    rs.Open Cmd1
    Worksheets("Procedure Export").Range("A1").CopyFromRecordset rs

    Call DumpSP("prcGetData", "", "", Worksheets("Procedure Export").Range("A1"))

End Sub

我收到一条关于未定义用户定义类型的错误。

Answer 1

要使用 ADO，请在 VBA IDE 中单击“工具”->“引用”并勾选“Microsoft ActiveX 数据对象” - 最好是其最高版本。

此外，您使用cn作为连接，但它未在该子中定义(假设它不是全局的)，并且您可能需要Set Cmd1.ActiveConnection = cn。

另请查看this ，它提前定义输入 (adParaminput) 参数，而不是使用 .Refresh，后者效率相当低(需要访问服务器)

更新示例:

rem for create procedure callstatisticsbyQ (@i int, @c varchar(10)) as select 1234;

Dim cn As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Set Cmd1 = New ADODB.Command

cn.Open "Provider=SQLNCLI10;Server=1.2.3.4;Database=x;Uid=x; Pwd=x;"

Set Cmd1.ActiveConnection = cn
Cmd1.CommandText = "callstatisticsbyQ"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Append Cmd1.CreateParameter("p1", adInteger, adParamInput, , Worksheets("Sheet2").Range("A1"))
Cmd1.Parameters.Append Cmd1.CreateParameter("p2", adVarChar, adParamInput, 20, Worksheets("Sheet2").Range("A2"))

Set rs = Cmd1.Execute()
MsgBox rs(0)

rs.Close
cn.Close