Old paging Stored Procedure

In ASP. the DataGrid in net controls its built-in paging function, which reads all the data from the database before paging. If the data volume is large, the default paging method is very inefficient. Let's see what pages we use in SQL 2000.

Create proc [DBO]. [page_table]
(
@ Pageno int, -- current page number. The minimum value is 1, indicating the first page.
@ Pagesize int, -- number of entries per page
@ Pagecount int out, -- get the total number of pages
)
As
Set nocount on -- the returned results do not contain information about the number of rows affected by the transact-SQL statement.

Declare @ start int, @ itemscount int
-- Retrieve the total number of pages
Select @ pagecount = ceiling (count (ID) + 0.0)/@ pagesize)
From table
Where condition
-- Paging
If (@ pageno <> 1) // whether it is the first page
Begin
Set @ itemscount = (@ pageNo-1) * @ pagesize + 1
Set rowcount @ itemscount
Select @ start = ID
From table
Where condition
Order by ID DESC

Set rowcount @ pagesize // Retrieve the number of entries displayed on each page
Fields required for SELECT query
From table
Where Id >= @ Start and other conditions // ID >=@ itemscount
Order by ID DESC
End
Else // query the first page
Begin
Set rowcount @ pagesize // obtain the number of entries displayed on each page
Fields required for SELECT query
From table
Where condition
Order by ID DESC
End
Set rowcount 0

In this way, only the "number of entries per page" is retrieved at a time, which greatly improves the efficiency.