将存储过程返回值分配给VBA变量

This should be real easy, but I haven't found a real concise answer yet. I have a very simple stored procedure in sql server that returns an integer value. All I want to do is get that return value into a variable for use in Access.

这应该是真的很容易,但我还没有找到一个真正简洁的答案。我在sql server中有一个非常简单的存储过程,它返回一个整数值。我想要做的就是将返回值转换为变量以便在Access中使用。

Stored Procedure:

ALTER PROCEDURE [dbo].[out_GetNextID]
@NextSumID integer
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @NextSumID = IDENT_CURRENT('Outage Summary')+IDENT_INCR('Outage Summary')
RETURN @NextSumID
END

I am using ADODB to execute the stored procedure, and I feel dumb for having to ask this, but how do I access the return value in Access after I run cmd.Execute? Thanks in advance and sorry for the lame question.

我正在使用ADODB来执行存储过程,我不得不问这个问题,但是在运行cmd.Execute之后如何访问Access中的返回值?在此先感谢并抱歉这个蹩脚的问题。

2 个解决方案

#1

There are two approaches, using output parameters or setting a ReturnValue (discussed below). For OUTPUT parameters, quoting from this SO link:

有两种方法,使用输出参数或设置ReturnValue(下面讨论)。对于OUTPUT参数,引用此SO链接:

essentially you just need to create a SqlParameter, set the Direction to Output, and add it to the SqlCommand's Parameters collection. Then execute the stored procedure and get the value of the parameter.

基本上你只需要创建一个SqlParameter,将Direction设置为Output,并将其添加到SqlCommand的Parameters集合中。然后执行存储过程并获取参数的值。

See the code from that page.

请参阅该页面中的代码。

However, you also need to include the word OUT (or OUTPUT) in your variable declaration:

但是,您还需要在变量声明中包含单词OUT(或OUTPUT):

@NextSumID integer OUT

When you declare the variable as OUT (or OUTPUT) it will be returned automatically, with whatever value it has when the procedure finishes, so you could just use RETURN.

当您将变量声明为OUT(或OUTPUT)时,它将自动返回,具有程序完成时的任何值,因此您可以使用RETURN。

Return Data from a Stored Procedure :MSDN

从存储过程返回数据:MSDN

You can, instead, use RETURN @NextSumID because you are just returning a single, integer, value. For this approach, you need to specify the parameter as the ReturnValue:

相反,您可以使用RETURN @NextSumID,因为您只返回一个整数值。对于此方法,您需要将参数指定为ReturnValue:

theParameter.Direction = ParameterDirection.ReturnValue

This approach is discussed further here (MSDN).

这种方法在这里进一步讨论(MSDN)。

#2

Here's how you would get the return value using a stored procedure that returns a value. You'll need reference to Microsoft ActiveX Data Objects 2.8 Library.

以下是使用返回值的存储过程获取返回值的方法。您需要参考Microsoft ActiveX Data Objects 2.8 Library。

Sub CheckValue(ByVal NextSumID As Long)

    '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 = "out_GetNextID"
    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("NextSumID", adVarChar, adParamInput, 10, NextSumID)

    'execute query
    Call ACmd.Execute

    'get return value
    Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")

    ACon.Close

End Sub

#1