Retrieving a recordset from a stored procedure

[RSfromCO]

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
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

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

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    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
END

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.

 

[PWise]

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

adoCmd(0)'RETURN
adoCmd(1)'UserId
...

or
adoCmd("@RETURN_VALUE")'RETURN
adoCmd("@UserId")'UserId
....

 

[RSfromCO]

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.

 

[PWise]

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
InitializeAdo
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

 

[PWise]

what version of sql are you using

 

[RSfromCO]

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.

 

[PWise]

this is my stored procedure

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

    RETURN 0
end

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

 

[PWise]

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

 

[RSfromCO]

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

SET NOCOUNT ON

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.

 

[PWise]

i changed the procedure to and still can access the @RETURN_VALUE

ALTER proc test
@testpram int=0
as
begin
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0
end
[/code

 

[PWise]

changed to

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

    RETURN 0
end

still returns record set and still can access @RETURN_VALUE

 

[cmmrfrds]

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.

rsReturned.Close
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