VBA：使用两个参数调用SQL Server存储过程

Question

就像标题中提到的，我只想从VBA调用SQL Server存储过程。

我可以打电话给我的存储过程是这样的：

EXEC dbo.spClientXLS @Nr = ' 131783', @date = '21.09.2014' 

Nr是varChar(50)类型值，date的类型是现在date

，如果我想从VBA调用它，我得到一条错误消息。我在VBA代码：

...'SQL Server stored procedure which is to execute with parameters 
Dim ADODBCmd As New ADODB.Command 
With ADODBCmd 
    .ActiveConnection = objconn 
    .CommandTimeout = 500 
    .CommandText = "dbo.spClient" 
    .CommandType = adCmdStoredProc 
End With 
Set recordset = ADODBCmd.Execute(, date, Nr) 

Date是Date类型，Nr是String类型。

我会很高兴，如果有人能解释我，我怎么能用两个参数来处理它。

Regards

Answer 1

试试这个。

Dim cmd As New ADODB.Command 
Dim rs As ADODB.Recordset 

With cmd 
    .ActiveConnection = objcnn 
    .CommandText = "spClient" 
    .CommandType = adCmdStoredProc 
    .Parameters.Refresh 
    If .Parameters.Count = 0 Then 
     .Parameters.Append cmd.CreateParameter("@Nr", adVarChar, adParamInput, 50) 
     .Parameters.Append cmd.CreateParameter("@date", adDate, adParamInput) 
    End If 
    .Parameters.Item("@Nr").Value = "131783" 
    .Parameters.Item("@date").Value = "09/21/2014" 
    Set rs = .Execute() 

End With 

Answer 2

您需要在代码中添加命令参数以接受参数值。检查以下代码：

Set oConn = New ADODB.Connection 
    Set rs = New ADODB.Recordset 
    Set cmd = New ADODB.Command 

    oConn.Open strConn '' strConn will have your connection string 

    stProcName = "thenameofmystoredprocedurehere" 'Define name of Stored Procedure to execute. 

    cmd.CommandType = adCmdStoredProc 'Define the ADODB command 
    cmd.ActiveConnection = oConn 'Set the command connection string 
    cmd.CommandText = stProcName 'Define Stored Procedure to run 

    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7) 
    prmUser.Value = strUser 
    cmd.Parameters.Append prmUser 

    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput) 
    prmApplication.Value = 1 
    cmd.Parameters.Append prmApplication 

    Set rs = cmd.Execute 

    Range("A1").CopyFromRecordset rs '' to copy data in recordset to excel. 

'' or you can do like this 

    Set rs = cmd.Execute 
    Do Until rs.EOF 
     '' Do Something 
    rs.MoveNext 
    Loop