如何从SQL Server存储过程返回值，并在Access VBA利用它们(How to retur

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

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

我的VBA调用SP低于，但不会现在的工作，加入了新的参数之后，我不知道我要去哪里错了，我不断收到3708 - Parameter object is improperly defined. Inconsistent or incomplete information was provided. 3708 - Parameter object is improperly defined. Inconsistent or incomplete information was provided. ：

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使返回值“可读”。

编辑 - 我已经改变了问题要更具体有关返回值，而不能只是使用输出参数。

Answer 1:

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

Answer 2:

几种方法都可以回去使用VBA值。

记录
算上受影响的记录（仅适用于插入/更新/删除，否则-1）
输出参数
返回值

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

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数据对象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

Answer 3:

我最初考虑的输出参数，但不能找出如何让他们回（在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

这将返回folliwing： Operation appears to have failed, check the DataSets Listing... Also, the Server returned the following information: [| errStatusOK / False || countCurrent / 0 || countHistorical / 10 |] Operation appears to have failed, check the DataSets Listing... Also, the Server returned the following information: [| errStatusOK / False || countCurrent / 0 || countHistorical / 10 |]