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.

Community · Accepted Answer · 2017-05-23 11:46:08Z

5

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

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Jul 25, 2013 at 21:26

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

philthyfool Over a year ago

Thank you for your response. I decided to ditch the RETURN @NextSumID and just use RETURN because the key piece I was missing was simply to assign the variable by NextID = cmd.Parameters("@NextSumID"). I don't know why it was so hard for me to figure that out.

Andy G Over a year ago

It took me a while to collate the information for my answer as well ;)

philthyfool Over a year ago

I feel as though there should be some better documentation/examples somewhere for basic usage of stored procedure output variables. It's real easy to find situations where people are updating recordsets and whatnot, but not many simple usage situations. Hope this helps someone else in a similar predicament as I was.

D_Bester · Accepted Answer · 2015-07-31 14:01:31Z

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

Collectives™ on Stack Overflow

Assign a stored procedure return value to a VBA variable

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related