Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Retrieving a recordset from a stored procedure

Retrieving a recordset from a stored procedure

Retrieving a recordset from a stored procedure

I am having a problem retrieving a record from a stored procedure.  I am new to this, and have had success retrieving an output parameter, but now I want to retrieve an entire record.

The procedure I am trying to use is defined as...


ALTER PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               uniqueidentifier,
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
    IF ( @UpdateLastActivity = 1 )
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0

The VB code within MS-Access looks something like...


Dim adoCmd As New ADODB.Command
Dim rsReturned As New ADODB.Recordset
With adoCmd
      .ActiveConnection = CurrentProject.Connection
      .CommandText = "aspnet_Membership_GetUserByUserId"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@UserID", adGUID, adParamInput, , Me.aspnet_UserID)
      .Parameters.Append .CreateParameter("@CurrentTimeUtc", adDate, adParamInput, , Now())
      .Parameters.Append .CreateParameter("@UpdateLastActivity", adBoolean, adParamInput, , 1)
      Set rsReturned = .Execute
End With

The recordset rsReturned seems to be closed after the procedure is executed (like maybe no records are returned), but I am pretty sure with the data I feed it at run-time a record should be returned.

I would also like to know how to gain acccess to the value that is RETURNed at the end of the stored procedure (the 0 or -1 depending on @@ROWCOUNT).  That might help me know that a record is being SELECTED inside the stored procedure.

RE: Retrieving a recordset from a stored procedure

The Default properties of a ADODB.Command are the parameters
so you can use



RE: Retrieving a recordset from a stored procedure

There are 3 available parameters which I can access.  They are...

adoCmd(0) which is the same as adoCmd("@UserId")
adoCmd(1) which is the same as adoCmd("@CurrentTimeUtc")
adoCmd(2) which is the same as adoCmd("@UpdateLastActivity")

But those are the parameters I submitted to the procedure, so being able to retrieve them back, doesn't do me much good.

That still leaves me with the same question I had before.  I don't seem to be getting back a recordset, and I don't know how to access the RETURN value.

I can tell from the updated record in the dbo.aspnet_Users table that the first half of the stored procedure is running properly and my input paramaters are being accepted properly.

If I call the stored procedure in Query Analyzer (rather than through my application) I get back a record for my input UserID.

If I try to access adoCmd("@RETURN_VALUE") as suggested above, it produces a run-time error saying the item cannot be found in the collection.

RE: Retrieving a recordset from a stored procedure

this is code that i use and it returns cmd.("@RETURN_VALUE")


Option Compare Database
Option Explicit
Public cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Function InitializeAdo()
If cnn.State = adStateClosed Then
    cnn.ConnectionTimeout = 0
    cnn.Open CurrentProject.Connection
End If
End Function

Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
cmd.CommandText = AdoString
Set cmd.ActiveConnection = cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters.Item(Prams) = AdoPrams(Prams)
    'Debug.Print cmd.Parameters.Item(Prams).Name; cmd.Parameters.Item(Prams).Value
Next Prams

Set ExecuteAdoRS = cmd.Execute(a)
End Function

this is how i call this function


Sub sss()
Dim rs As ADODB.Recordset

Set rs = ExecuteAdoRS("test", 4, 0)
End Sub

RE: Retrieving a recordset from a stored procedure

what version of sql are you using

RE: Retrieving a recordset from a stored procedure

It says version 8 (which I think is 2000).

Your example doesn't show the code for the stored procedure, but I would guess it has a line something to the effect...

@RETURN_VALUE    nvarchar(256) OUTPUT

Which would define why you have access to a return value called @RETURN_VALUE

The stored procedure that I have to deal with in this case does not have such a parameter defined, but still uses the lines "RETURN -1" and "RETURN 0" towards the end, so I was wondering if I could access those values somehow.  It looks like if there should be a recordset returned by the procedure the returned value would = 0, otherwise it would = -1.

Is it possible... that since the RETURN 0 and RETURN -1 lines come after the SELECT statement, that what is actually being returned to my application is not the result from the SELECT statment (a recordset) but rather
the integer value 0 or -1?

For other stored procedures that I have, there is an OUTPUT parameter defined, which then I can access.  This stored procedure does not have such an OUTPUT parameter defined.  I would prefer not to modify the procedure as it is not mine.

RE: Retrieving a recordset from a stored procedure

this is my stored procedure


CREATE proc test
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0
i am also using sql 2000 every sp has a bulit in return value that has a default value of 0

RE: Retrieving a recordset from a stored procedure

did yuo try my code and still
"get item cannot be found in the collection. "

RE: Retrieving a recordset from a stored procedure

Well..... If I add the line...


to the stored procedure, it appears to now return a recordset... which is all I was trying to do in the first place.  So now my question is, what does SET NOCOUNT ON do to the stored procedure that otherwise that line should not be there.

FYI... even though it works (as far as returning a recordset), I still can't access a parameter called @RETURN_VALUE.  But, when I create a simple stored procedure that does not define any parameters (like your example) then there is such a parameter returned.

RE: Retrieving a recordset from a stored procedure

i changed the procedure  to and still can access the @RETURN_VALUE
ALTER proc test
@testpram int=0
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0

RE: Retrieving a recordset from a stored procedure

changed to


CREATE proc test
@testpram int=0
set nocount off
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0

still returns record set and still can access  @RETURN_VALUE

RE: Retrieving a recordset from a stored procedure

The return parameter must be the first one in the parameters collection and indicate the type.

 .Parameters.Append .CreateParameter("@ret", adInteger, adParamReturnValue)

Also, you may need to process and close the recordset before referencing the Return Value.

Print.Debug "return value = "; @ret

The insert statement in your stored procedure will generate informational recordset(s) which can be suppressed with the "Set nocount on". This is the standard way to get rid of the extra recordsets.

You can actually loop through the recordsets in ADO, but this is a lot of extra work and unnecessary.
rsReturned = rsReturned.NextRecordset

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close