ASP returns stored procedures for multiple recordsets

To return a stored process for more than one recordset
This article first describes a stored process that returns a recordset. Sometimes, you need a storage process to return multiple recordsets, how do you get them at the same time in the ASP? To illustrate this issue, add two fields to the UserInfo table: Usertel and Usermail, and set only the Logged-in users to see both.

/*sp7*/
CREATE PROCEDURE DBO. GetUserInfo
@USERID INT,
@CHECKLOGIN BIT
As
SET NOCOUNT on
BEGIN
IF @USERID is null OR @CHECKLOGIN be NULL return
SELECT USERNAME
From DBO. [Usrinfo]
WHERE userid= @USERID
--If you are a registered user, take Usertel and Usermail
IF @CHECKLOGIN =1
SELECT Usertel,usermail
From DBO. [USERINFO]
WHERE userid= @USERID
Return
End
Go

The following is the ASP code:

' * * Calls a stored process that returns more than one Recordset * *
DIM Checklg,userid,username,usertel,usermail
DIM Mycomm,myrst
USERID = 1
' Checklogin () is a custom function that determines whether the visitor is logged in
CHECKLG = Checklogin ()
SET Mycomm = SERVER. CreateObject ("Adodb.command")
With Mycomm
. ActiveConnection = Myconstr ' myconstr is a database connection string
. CommandText = "GetUserInfo" ' specifies the name of the stored process
. CommandType = 4 ' indicates that this is a stored process
. PREPARED = TRUE ' requires that SQL commands be compiled in advance
. PARAMETERS. APPEND. CreateParameter ("@USERID", 3,1,4,userid)
. PARAMETERS. APPEND. CreateParameter ("@CHECKLOGIN", 11,1,1,CHECKLG)
SET Myrst =. EXECUTE
End With
SET Mycomm = Nothing

' Take values from the first set of records
USERNAME = Myrst (0)
' Take values from the second recordset
IF not Myrst are nothing THEN
SET Myrst = Myrst. NextRecordset ()
Usertel = Myrst (0)
Usermail = Myrst (1)
End IF
SET Myrst = Nothing

In the above code, the Recordset object's NextRecordset method is used to obtain multiple recordsets returned by the storage process.

At this point, this article has made a more comprehensive explanation for the various situations that the ASP invokes the storage process. And finally, in an ASP program, the different methods of calling multiple stored processes.