Row_number implementation paging (for SQL)

1: First of all

Select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1

Generate a collection with an ordinal

2: Re-query the 1th to 5th data of the collection

SELECT * FROM
(select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1) as Temp
where RowNumber between 1 and 5

The full SQL statement

declare @pagesize int; declare @pageindex int; Set @pagesize = 3

Set @pageindex = 1; --First page

SELECT * FROM (select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1) as temp where rowNumber between (( (@pageindex-1) * @pagesize) +1) and (@pageindex * @pagesize)

Set @pageindex = 2; --second page

SELECT * FROM (select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1) as temp where rowNumber between (( (@pageindex-1) * @pagesize) +1) and (@pageindex * @pagesize)

Set @pageindex = 3; --Page Three

SELECT * FROM (select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1) as temp where rowNumber between (( (@pageindex-1) * @pagesize) +1) and (@pageindex * @pagesize)

Set @pageindex = 4;--Fourth page

SELECT * FROM (select Row_number () over (order by ID ASC) as ' RowNumber ', * from table1) as temp where rowNumber between (( (@pageindex-1) * @pagesize) +1) and (@pageindex * @pagesize)

Let's write a stored procedure paging

------------------------------

Alter Procedure Pagepager

@TableName varchar (80),

@File varchar (+),---

@Where varchar,---with and connections

@OrderFile varchar (100),--sort field

@OrderType varchar,--ASC: ORDER, desc: Reverse

@PageSize varchar (10),--

@PageIndex varchar (10)--

As

if (ISNULL (@OrderFile, ') = ')

Begin

Set @OrderFile = ' ID ';

End

if (ISNULL (@OrderType, ') = ')

Begin

Set @OrderType = ' ASC '

End

if (ISNULL (@File, ') = ')

Begin

Set @File = ' * '

End

DECLARE @select varchar (8000)

Set @select = ' SELECT ' + @File + ' from (select *,row_number () over (order by ' + @OrderFile + "+ @Order Type + ') as ' rowNumber ' from ' + @TableName + ' where 1=1 ' + @Where + ') temp where rowNumber between (( ' + @PageIndex + '-1) * ' + @PageSize + ') +1) and (' + @PageIndex + ' * ' + @PageSize + ') '

EXEC (@select)

Row_number implementation paging (for SQL)