7. Returns stored procedures for multiple record sets
The first thing that this article introduces is the stored procedure that returns a record set. Sometimes, a stored procedure needs to return multiple record sets. In ASP, how to obtain these record sets at the same time? To illustrate this problem, add two fields to the userinfo table: usertel and usermail, and set that only logged-in users can view these two contents.
/*SP7*/
CREATE PROCEDURE
@userid int,
@checklogin bit
as
set nocount on
begin
if @userid is null or @checklogin is null return
select username
from dbo.[usrinfo]
where userid=@userid
--If it is a logged in user, use usertel and usermail
if @checklogin=1
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
return
end
go
The following is the ASP code:
'**Calling stored procedures that return multiple recordsets**
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
'checklogin() is a custom function to determine whether the visitor is logged in
checklg = checklogin()
Set MyComm = ("")
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string
.CommandText = "getUserInfo" 'Specify the stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Require SQL command to be compiled first
. .CreateParameter("@userid",3,1,4,UserID)
. .CreateParameter("@checklogin",11,1,1,checklg)
Set MyRst = .Execute
end with
Set MyComm = Nothing
'Get the value from the first record set
UserName = MyRst(0)
'Get the value from the second record set
if not MyRst is Nothing then
Set MyRst = ()
UserTel = MyRst(0)
UserMail = MyRst(1)
end if
Set MyRst = Nothing
In the above code, multiple recordsets returned by the stored procedure are obtained using the NextRecordset method of the Recordset object.
So far, this article has made a relatively comprehensive explanation of various situations in which ASP calls stored procedures. Finally, let’s talk about different methods of calling multiple stored procedures in an ASP program.
In an ASP program, it is feasible to call multiple stored procedures with at least three methods:
1. Create multiple Command objects
DIM MyComm
Set MyComm = ("")
'Call stored procedure one
......
Set MyComm = Nothing
Set MyComm = ("")
'Call stored procedure two
......
Set MyComm = Nothing
......
2. Create only one Command object, and clear its parameters when one call is finished.
DIM MyComm
Set MyComm = ("")
'Call stored procedure one
.....
'Clear parameters (assuming there are three parameters)
2
1
0
'Call stored procedure two and clear parameters
......
Set MyComm = Nothing
At this time, you should note: the order of clearing parameters is the opposite of the order of parameter declarations. I don’t know the reason.
3. Reset the Parameter object using the Refresh method of the Parameters data collection
DIM MyComm
Set MyComm = ("")
'Call stored procedure one
.....
'Reset all Parameter objects contained in the Parameters data collection
'Call stored procedure two
.....
Set MyComm = Nothing
It is generally believed that repeatedly creating objects is a relatively inefficient method, but after testing (the test tool is Microsoft Application Center Test), the results are unexpected:
Method 2 > = Method 1 > > Method 3
The running speed of method 2 is greater than or equal to Method 1 (maximum 4% higher), and the running speed of these two methods is much greater than Method 3 (maximum 130%). Therefore, it is recommended to use Method 1 when there are many parameters, and when there are fewer parameters, use Method 2.
It took a day to finally write down some of my superficial experiences in calling stored procedures in ASP. Some of these are things that I only know the result but not the cause, and some may be wrong, but these are all practiced by me personally. Dear readers, please accept it critically.