To demonstrate the call method in this case, we will improve the above example. It is still obtained the username of the user with ID 1, but it is possible that the user does not exist (the user has been deleted, and userid is a self-growing field). Stored procedures return different values depending on whether the user exists or not. At this time, the stored procedure and ASP code are as follows:
|
The following is the quoted content: /*SP5*/ CREATE PROCEDURE --In order to deepen the impression of "order", reverse the definition order of the following two parameters @UserName varchar(40) output, @UserID int as set nocount on begin if @UserID is null return select @UserName=username from dbo.[userinfo] where userid=@UserID if @@rowcount>0 return 1 else return 0 return end go |
'**Call stored procedures with return values, input parameters, and output parameters**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = ("")
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string
.CommandText = "getUserName" 'Specify the stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Requires SQL command to be compiled first
'The return value must be declared first
. .CreateParameter("RETURN",2,4)
'The declaration order of the following two parameters is also reversed accordingly
. .CreateParameter("@UserName",200,2,40)
. .CreateParameter("@UserID",3,1,4,UserID)
.Execute
end with
if MyComm(0) = 1 then
UserName = MyComm(1)
else
UserName = "This user does not exist"
end if
Set MyComm = Nothing
6. Stored procedures that return both parameters and recordsets
Sometimes, we need to return parameters and record sets at the same time, for example, when using stored procedures to paginate, we need to return parameters such as the record set and the total data volume at the same time. The following is a stored procedure for paging:
|
The following is the quoted content: /*SP6*/ |
--Acquiring the total number of records
declare @iRecordCount int
set @iRecordCount = @@rowcount
--Determine the total number of pages
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
--If the requested page number is greater than the total number of pages, the last page will be displayed
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
--Determine the whole record of the current page
DECLARE @iStart int --start record
DECLARE @iEnd int --end record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
--Get the current page record
select * from #t where ID>@iStart and ID<@iEnd
--Delete temporary table
DROP TABLE #t
--Return the total number of records
return @iRecordCount
end
go
In the above stored procedure, enter the current page number and the number of records per page to return the record set of the current page, the total number of pages and the total number of records. For more typicality, the total number of records is returned as a return value. The following is the ASP code that calls the stored procedure (specific paging operations are omitted):
'**Calling paging stored procedure**
DIM pagenow,pagesize,pagecount,recordcount
DIM MyComm,MyRst
pagenow = Request("pn")
'Custom functions are used to verify natural numbers
if CheckNar(pagenow) = false then pagenow = 1
pagesize = 20
Set MyComm = ("")
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string
.CommandText = "getUserList" 'Specify the stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Requires SQL command to be compiled first
'Return value (total record amount)
. .CreateParameter("RETURN",2,4)
'Export parameters (total page count)
. .CreateParameter("@iPageCount",3,2)
'Enter parameter (current page number)
. .CreateParameter("@iPage",3,1,4,pagenow)
'Input parameters (number of records per page)
. .CreateParameter("@iPageSize",3,1,4,pagesize)
Set MyRst = .Execute
end with
if = 0 then 'No data was retrieved, MyRst closes
recordcount = -1
else
'Note: To obtain parameter values, you must first close the record set object
recordcount = MyComm(0)
pagecount = MyComm(1)
if cint(pagenow)>=cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing
'The following display record
if recordcount = 0 then
"No record"
elseif recordcount > 0 then
do until
......
loop
'The following displays the page information
......
else 'recordcount=-1
"Error parameter"
end if
For the above code, there is only one thing to explain: when returning the record set and parameters at the same time, to obtain the parameters, you must first turn off the record set and then turn it on when using the record set.
7. Returns stored procedures for multiple recordsets
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.
|
The following is the quoted content: /*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 |
Here is the ASP code:
|
The following is the quoted content: **Call 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 'Requires 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 |
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 Parameter object using the Refresh method of Parameters data collection
DIM MyComm
Set MyComm = ("")
'Call stored procedure one
.....
'Reset all Parameter objects contained in 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.