Assign a stored procedure return value to a VBA variable

Question

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.

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.

Answer 1

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

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.

See the code from that page.

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

@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.

Return Data from a Stored Procedure :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:

theParameter.Direction = ParameterDirection.ReturnValue

This approach is discussed further here (MSDN).

Answer 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.

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