A stored procedure that returns more than one recordset-asp calls a stored procedure

Stored Procedure | Recordset This article first describes the stored procedures that return a recordset. Sometimes, you need a stored procedure 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:

' * * Call stored procedures that return multiple recordsets * * 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 stored procedure name . CommandType = 4 ' indicates that this is a stored procedure . 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, a Recordset object's NextRecordset method is used to obtain multiple recordsets returned by the stored procedure.

So far, this article has made a more comprehensive explanation for the various cases of ASP calling stored procedures. And finally, in an ASP program, the different methods of calling multiple stored procedures.

In an ASP program, it is possible to invoke multiple stored procedures in at least three ways:

　1. Create multiple Command objects

DIM Mycomm Set Mycomm = Server.CreateObject ("Adodb.command") ' Call a stored procedure ...... Set Mycomm = Nothing Set Mycomm = Server.CreateObject ("Adodb.command") ' Call stored procedure two ...... Set Mycomm = Nothing ......

　2. Create only one Command object and clear its arguments when the call is ended

DIM Mycomm Set Mycomm = Server.CreateObject ("Adodb.command") ' Call a stored procedure ..... ' Clear parameters (assuming three parameters) MyComm.Parameters.delete 2 MyComm.Parameters.delete 1 MyComm.Parameters.delete 0 ' Call stored procedure two and clear parameters ...... Set Mycomm = Nothing

Note that the order of the purge parameters is the opposite of the order of the parameter declarations, and I don't know why.

　　3. Reset the Parameter object using the Refresh method of the Parameters data collection

DIM Mycomm Set Mycomm = Server.CreateObject ("Adodb.command") ' Call a stored procedure ..... ' Resets all parameter objects contained in the Parameters data collection MyComm.Parameters.Refresh ' Call stored procedure two ..... Set Mycomm = Nothing

It is generally thought that creating objects repeatedly is a less efficient method, but after testing (the test tool is Microsoft Application Center test), the result is unexpected:

Method 2 >= Method 1 >> Method 3

Method 2 running speed is greater than or equal to Method 1 (up to 4% or so), the two methods are much faster than Method 3 (up to 130%), so the proposed parameters for a long time, using Method 1, in less than the parameter, the method 2.