如何从SQL Server存储过程中返回值并在Access VBA中使用

Question

我在SQL Server中设置了一个存储过程，它可以正常工作。现在我可以从VBA调用它，但是想要返回一个值以知道是否有任何错误等。我的SP中的最后一个参数设置为OUTPUT：

@DataSetID int = 0,
@Destination char(1)='-',
@errStatusOK bit OUTPUT

我的VBA调用存储过程的代码如下，但是在添加了新参数后它现在无法工作了，我不确定哪里出了问题，我一直收到错误信息3708 - 参数对象未正确定义。提供的信息不一致或不完整。：

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

如何在VBA中使用OUTPUT参数并使返回值可以被VBA“读取”。

编辑 - 我已将问题更改为更专门关于返回值而不仅仅是使用OUTPUT参数。

- aSystemOverload

Answer 1

使用VBA可以有多种方式获取返回值：

1. 记录集
2. 受影响的记录数量（仅适用于插入/更新/删除操作，否则为-1）
3. 输出参数
4. 返回值

我的代码演示了这四种方式。这是一个返回值的存储过程示例：

Create PROCEDURE CheckExpedite
    @InputX  varchar(10),
    @InputY int,
    @HasExpedite int out
AS
BEGIN
    Select @HasExpedite = 9 from <Table>
    where Column2 = @InputX and Column3 = @InputY

    If @HasExpedite = 9
        Return 2
    Else
        Return 3
End

以下是我在Excel VBA中使用的子程序。您需要引用Microsoft ActiveX Data Objects 2.8库。

Sub CheckValue()

    Dim InputX As String: InputX = "6000"
    Dim InputY As Integer: InputY = 2014

    'open connnection
    Dim ACon As New Connection
    'ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
    '    "Initial Catalog=<Table>;Integrated Security=SSPI")

    'set command
    Dim ACmd As New Command
    Set ACmd.ActiveConnection = ACon
    ACmd.CommandText = "CheckExpedite"
    ACmd.CommandType = adCmdStoredProc

    'Return value must be first parameter else you'll get error from too many parameters
    'Procedure or function "Name" has too many arguments specified.
    ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
    ACmd.Parameters.Append ACmd.CreateParameter("InputX", adVarChar, adParamInput, 10, InputX)
    ACmd.Parameters.Append ACmd.CreateParameter("InputY", adInteger, adParamInput, 6, InputY)
    ACmd.Parameters.Append ACmd.CreateParameter("HasExpedite", adInteger, adParamOutput)

    Dim RS As Recordset
    Dim RecordsAffected As Long

    'execute query that returns value
    Call ACmd.Execute(RecordsAffected:=RecordsAffected, Options:=adExecuteNoRecords)

    'execute query that returns recordset
    'Set RS = ACmd.Execute(RecordsAffected:=RecordsAffected)

    'get records affected, return value and output parameter
    Debug.Print "Records affected: " & RecordsAffected
    Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
    Debug.Print "Output param: " & ACmd.Parameters("HasExpedite")

    'use record set here
    '...

    'close
    If Not RS Is Nothing Then RS.Close
    ACon.Close

End Sub

- D_Bester

Answer 2

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param1 = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param2 = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param3 = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamOutput, , adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

- AnandPhadke

---

你一定是在我回答时发布了这个帖子，我现在明白我在CreateParameter上犯了什么错误。非常感谢，将来可能会用到。 - aSystemOverload

Answer 3

我最初查看了输出参数，但无法找到如何将它们返回到Access（在VBA中），然后向用户提供反馈。一位同事建议在存储过程中使用SELECT，并使用此方法。

存储过程： 在末尾添加以下内容：

SELECT @errStatusOK as errStatusOK, @countCurrent as countCurrent, @countHistorical as countHistorical

VBA:

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param As New ADODB.Parameter
Dim fld As ADODB.Field
Dim stMessage As String

Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
'rs.Open cmd
Set rs = cmd.Execute
If rs!errstatusok = True Then
    stMessage = "Operation appears to have been successful, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
Else
    stMessage = "Operation appears to have failed, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
End If
For Each fld In rs.Fields
    stMessage = stMessage & "| " & fld.Name & " / " & fld.Value & " |"
Next fld
stMessage = stMessage & "]"

MsgBox stMessage

这会返回以下内容：操作似乎失败了，请检查数据集列表... 此外，服务器返回以下信息：[| errStatusOK / False || countCurrent / 0 || countHistorical / 10 |]

- aSystemOverload

---

你当然可以使用输出参数，以及返回值或受影响的记录。请参见我的回答。 - D_Bester

Answer 4

除了“adParamInput”之外，还有其他参数枚举，其中之一是“adParamOutput”，用于指示存储过程的输出参数，“adParamInputOutput”用于既可输入又可输出的参数。在您的情况下，我认为使用“adParamOutput”是合适的。希望这就是您要找的内容。

- David W

---

不是我想要的，但很有用。一位同事建议我只需通过 SELECT @errStatusOK 等提供输出，并在 VBA 中进行操作，这非常有效。谢谢。 - aSystemOverload