SQL Stored Procedure-displays records in a table by page

When displaying the records in a table, you need to display the records by page because there are many records. Here, the SQL Stored procedure is used to display the records by page.
1. For database and field, see conn. asp.

Add several records to the table

2. Stored Procedure Code
Create procedure dbo. getUserList
@ 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 a temporary table
Create table # t (ID int IDENTITY, -- Auto-increment field
Y_id int,
Y_username varchar (40 ),
Y_password varchar (40 ))
-- Write data to the temporary table
Insert into # t
Select y_id, y_username, y_password from dbo. [user]
Order by y_id desc
       
-- Retrieve 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 page number, the last page is displayed.
IF @ iPage> @ iPageCount
SELECT @ iPage = @ iPageCount

-- Determine the start and end records of the current page
DECLARE @ iStart int -- start record
DECLARE @ iEnd int -- end record
SELECT @ iStart = (@ iPage-1) * @ iPageSize
SELECT @ iEnd = @ iStart + @ iPageSize + 1

-- Retrieve the current page record
Select * from # t where ID> @ iStart and ID <@ iEnd

-- Delete a temporary table
Drop table # t

-- Total number of returned Records
Return @ iRecordCount
End
GO


3. Display record list2.asp
<! -- # Include file = "conn. asp" -->
<%
'** Call the paging stored procedure **
DIM pagenow, pagesize, pagecount, recordcount
DIM MyComm, MyRst
Pagenow = Request ("pn ")
'Udf is used to verify the natural number
If pagenow = "" then pagenow = 1
Pagenow = CInt (pagenow)
Pagesize = 2
Set MyComm = Server. CreateObject ("ADODB. Command ")
With MyComm
. ActiveConnection = Conn' conn is the database connection string
. CommandText = "getUserList" 'specifies the name of the stored procedure.
. CommandType = 4' indicates that this is a stored procedure
. Prepared = true' requires that the SQL command be compiled first
'Return value (total number of records)
. Parameters. Append. CreateParameter ("RETURN", 2, 4)
'Output parameter (total number of pages)
. Parameters. Append. CreateParameter ("@ iPageCount", 3, 2)
'Input parameter (current page number)
. Parameters. append. CreateParameter ("@ iPage", 3, 1, 4, pagenow)
'Input parameter (number of records per page)
. Parameters. append. CreateParameter ("@ iPageSize", 3, 1, 4, pagesize)
Set rs =. Execute
End
If rs. state = 0 then' is not retrieved, rs is disabled.
Recordcount =-1
Else
Rs. close 'Note: To obtain the parameter value, you must first close the record set object
Recordcount = MyComm (0)
Pagecount = CInt (MyComm (1 ))
If cint (pagenow)> = cint (pagecount) then pagenow = pagecount
End if
Set MyComm = Nothing

'The following records are displayed:
If recordcount = 0 then
Response. Write "no record"
Elseif recordcount> 0 then
Rs. open
Do While Not rs. eof
Response. write rs ("y_id") & ":" & rs ("y_username") & "----" & rs ("y_password") & "<br>"
Rs. movenext
Loop
******** **************
If pagenow> 1 Then
Response. write "<a href = ""? Pn = 1 ""> homepage </a>"
Else
Response. write "homepage"
End If
If pagenow> 1 Then
Response. write "<a href = ""? Pn = "& pagenow-1 &" "> Previous </a>"
Else
Response. write "previous page"
End If
If pagenow <pagecount Then
Response. write "<a href = ""? Pn = "& pagenow + 1 &" "> Next page </a>"
Else
Response. write "next page"
End If
If pagenow <pagecount Then
Response. write "<a href = ""? Pn = "& pagecount &" "> Last page </a>"
Else
Response. write "last page"
End if
Else 'recordcount =-1
Response. Write "Parameter error"
End If
***************** ************
%>

4,

5. Complete. Run the list2.asp file to view the content. Click the home page, previous page, next page, and last page to experience the paging experience.