Two common SQL paging Algorithms

Today, I spent two hours writing two paging algorithms.

-- Not in: This method is used to query the data in front of a table.
Create procedure Page_proc
(
@ PageSize int, -- page number,
@ PageIndex int, -- page number
@ TableName varchar (50), -- table name
@ Mast varchar (50) -- Name of the table's primary key
)
As
Begin
Declare @ arg int
Set @ arg = @ pageSize * (@ pageIndex-1)
 
-- Use dynamic SQL
Declare @ strSql varchar (1000)
Set @ strSql = 'select top ('+ cast (@ pageSize as varchar (20) +') * from' + @ tableName
+ 'Where' + @ mast + 'not in (select top ('+ cast (@ arg as varchar (20) + ') '+ @ mast + 'from' + @ tableName + ')'
Print @ strSql
Exec (@ strSql)
End

 

-- The forward and reverse order method is used to query the data behind a table with high efficiency.

Create proc FenYe
@ TableName varchar (50), -- table name
@ Mast varchar (50), -- Name of the table's primary key
@ PageIndex int, -- page number
@ PageCount int -- number of pages
As
Begin
-- Obtain the total number of rows of the table. Because it is a dynamic SQL statement that assigns a value to @ RowCount, sp_executesql is required.
Declare @ RowCount int
Declare @ countSql nvarchar (200)
Set @ countSql = 'select @ innerCount = count (*) from' + @ tableName

Exec sp_executesql @ countSql, n' @ innerCount int output', @ RowCount output
-- Print @ RowCount
-- Obtain the number of rows to be queried in the subquery.
Declare @ Count int
Set @ Count = @ RowCount-(@ pageIndex-1) * @ pageCount

-- If the query conditions do not match, the stored procedure ends.
If (@ Count <0)
Return;
-- Use dynamic SQL
Declare @ strSql varchar (1000)
Set @ strSql = 'select top '+ cast (@ pageCount as varchar (20) +' * from' + '(select top' +
Cast (@ Count as varchar (20) + '* from' + cast (@ tableName as varchar (50) + 'ORDER'
+ Cast (@ mast as varchar (50) + 'desc' as temp order by' + cast (@ mast as varchar (50 ))

Print @ strSql
Exec (@ strSql)
End

These two are the most commonly used ones. The first release of technical essays, please understand the shortcomings.

 

 

A successful example of a successful query of the data in front of a table using forward reverse FLASHBACK:

 

Select * from (select top 10 * from (select top 12 * from A0002 order by ID asc) as temp order by ID desc) as tt order by ID

An example of an error occurred while querying the data in front of a table using forward reverse FLASHBACK:

Select top 10 * from (select top 12 * from A0002 order by ID asc) as temp order by ID asc