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.

score 5 · Accepted Answer · edited May 23 '17 at 11:46

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 '17 at 11:46

Community

1
1

answered Jul 25 '13 at 21:26

Andy G

19,232
5
47
69

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. – philthyfool Jul 25 '13 at 23:33
It took me a while to collate the information for my answer as well ;) – Andy G Jul 25 '13 at 23:42
1

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. – philthyfool Jul 29 '13 at 17:15

D_Bester · Answer 2 · 2015-07-31T14:01:31.723

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

Assign a stored procedure return value to a VBA variable

2 Answers2

Linked