在 VBA 中执行带参数的 SQL 存储过程

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/13631280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 18:38:40  来源:igfitidea点击:

Executing a SQL stored procedure with parameters in VBA

stored-proceduresexcel-vbavbaexcel

提问by Marc Jason

I'm fairly new to this. I'm trying to write a procedure in VBA that executes a stored procedure from excel and returns the record set. It currently returns the results of the stored procedure in spreadsheet 2 but I've had to hard code the parameter into the SQL code. Here is what I have pinched from other online forums.

我对此很陌生。我正在尝试在 VBA 中编写一个过程,该过程从 excel 执行存储过程并返回记录集。它目前返回电子表格 2 中存储过程的结果,但我不得不将参数硬编码到 SQL 代码中。这是我从其他在线论坛中摘录的内容。

Option Explicit

Public Sub OpenConnection()

'Set the variables
Dim conn As ADODB.Connection
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim fld
Dim i As Integer

'Error handler
On Error GoTo errlbl

'Open database connection
Set conn = New ADODB.Connection

'First, construct the connection string.

'ODBC CONNECTION YOU'VE ALREADY SET UP:
conn.ConnectionString = "DSN=PC_Tool_Coding"

conn.Open       'Here's where the connection is opened.

Debug.Print conn.ConnectionString  'This can be very handy to help debug!

'Recordset
Set rs = New ADODB.Recordset

str = "exec Select_account_info"

'recordset is opened here
rs.Open str, conn, adOpenStatic, adLockReadOnly

If Not IsEmptyRecordset(rs) Then
    rs.MoveFirst

    'Populate the first row of the sheet with recordset's field names
    i = 0
    For Each fld In rs.Fields
        Sheet2.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
        i = i + 1
    Next fld
    'Populate the sheet with the data from the recordset
    Sheet2.Range("A2").CopyFromRecordset rs


Else
    MsgBox "Unable to open recordset, or unable to connect to database.", _
       vbCritical, "Can't get requested records"

End If

'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

exitlbl:
  Debug.Print "Error: " & Err.Number
  If Err.Number = 0 Then
    MsgBox "Done", vbOKOnly, "All Done."
  End If
  Exit Sub
errlbl:
   MsgBox "Error #: " & Err.Number & ", Description:  " & Err.Description, vbCritical, "Error in OpenConnection()"
Exit Sub
'Resume exitlbl
End Sub

I've looked around on how to get it to work with parameters and I just can't seem to get there. The parameter I'll be using is called @accgrpnum in SQL. Its a 12 letter string.

我环顾四周如何让它与参数一起工作,但我似乎无法到达那里。我将使用的参数在 SQL 中称为 @accgrpnum。它是一个 12 个字母的字符串。

Many thanks to any help in advance.

非常感谢提前提供的任何帮助。

回答by Jacob Young

Take a look at bonCodigo's link or

看看bonCodigo的链接或

I've also been able to do this (with SQL Server 2008) by sending more than one statement, but separated in a SQL statement like

我也可以通过发送多个语句来做到这一点(使用 SQL Server 2008),但在 SQL 语句中分开,例如

str = "SET @accgrpnum = 'my_account'; exec Select_account_info;"