Getting started with stored procedures under ASP, page 4/5

6. Stored procedures that return parameters and record sets at the same time
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:
/*SP6*/ 
CREATE PROCEDURE  
@iPageCount int OUTPUT, -Total number of pages
@iPage int, --Current page number
@iPageSize int --Number of records per page
as 
set nocount on 
begin 
--Create temporary table
create table #t (ID int IDENTITY, --Auto-increase field
userid int, 
username varchar(40)) 
--Write data to temporary table
insert into #t 
select userid,username from dbo.[UserInfo] 
order by userid 
--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 number of pages)
. .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 closed
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 
"Parameter error"
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.