通过 ADO/VBA 指示 SQL Server 存储过程更新成功/失败的返回值

Return value indicating update success/failure of SQL Server stored procedure via ADO/VBA

提问by webworm

I have a SQL Server 2008 stored procedure that updates values in a table. I would like to have the stored procedure return an integer value indicating that the update was successful (return 0) or not (returns error number). What would be the best way to accomplish this via ADO and VBA? Here some of my code in simplified form that performs the update ... I am just not sure how to get back the return value of the stored procedure

我有一个更新表中值的 SQL Server 2008 存储过程。我想让存储过程返回一个整数值，指示更新成功（返回 0）或不成功（返回错误号）。通过 ADO 和 VBA 实现这一目标的最佳方法是什么？这是我的一些执行更新的简化形式的代码......我只是不确定如何取回存储过程的返回值

Public Function DoUpdate(employeeID as integer, firstName as string, lastname as string) as integer

  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim activeConnectionString As String

  activeConnectionString = GetActiveConnectionString()

  Set cnn = New ADODB.Connection
  cnn.ConnectionString = activeConnectionString
  cnn.CursorLocation = adUseClient
  cnn.Open

  Set cmd = New ADODB.Command
  cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "uspUpdateEmployeeName"
  cmd.NamedParameters = True

  cmd.Parameters("@EmployeeID").Value = employeeID 
  cmd.Parameters("@FirstName").Value = firstName
  cmd.Parameters("@LastName").Value = lastName

  cmd.Execute

  'Unsure of how to get back return value here
  'DoUpdate = returnValue

  Set cnn = Nothing

End Function

回答by David Wilson

Note: The return_valuemust be the first parameter!

注意：return_value必须是第一个参数！

Order matters.

订单很重要。

I was getting errors stating that my query "had too many arguments"when I had specified my return_valueparameter last, instead of first.

"had too many arguments"当我return_value最后而不是第一个指定参数时，我收到错误消息，指出我的查询。

The parameters' ordering was the cause of my error.

参数的顺序是我出错的原因。

回答by Fionnuala

If you use

如果你使用

Dim lngRecs As Long

cmd.Execute lngRecs

lngRecs should contain records affected.

lngRecs 应包含受影响的记录。

回答by edosoft

I seem to remember that you need to supply an extra parameter with the type 'adParamReturnValue' like this:

我似乎记得你需要提供一个类型为“adParamReturnValue”的额外参数，如下所示：

Dim lRetVal as Long

Set cmd = New ADODB.Command
cmd.Parameters.Append .CreateParameter("returnvalue", adInteger, adParamReturnValue)

cmd.Execute

'Now check the return value of the procedure
lRetVal = cmd.Parameters("returnvalue")

If lRetVal > 0 then

回答by D_Bester

Several ways are possible to get values back using VBA:

有几种方法可以使用 VBA 取回值：

1. Recordset
2. Count of records affected (only for Insert/Update/Delete otherwise -1)
3. Output parameter
4. Return value

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

My code demonstrates all four. Here is a stored procedure that returns a value:

我的代码演示了所有四个。这是一个返回值的存储过程：

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

Here is the sub I use in Excel VBA. You'll need reference to Microsoft ActiveX Data Objects 2.8 Library.

这是我在 Excel VBA 中使用的子程序。您需要参考 Microsoft ActiveX Data Objects 2.8 Library。

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