Implementation of SQL Server using Row_number paging _mssql

This article for everyone to share the SQL Server using Row_number pagination implementation method for your reference, the specific contents are as follows

1, First is

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

To generate a collection with ordinal numbers

2, and then query the collection of the 1th to 5th data

  SELECT * FROM 
  (select Row_number () "Over" (Order by ID ASC) as "RowNumber", * from table1) as temp
  where RowNumber is Tween 1 and 5

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 @pagei Ndex = 2; --The 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;--third 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 = 4;--Fourth page

select * FROM (select Row_number () over (order B Y ID asc) as ' RowNumber ', * from table1) as temp where rownumber between ((@pageindex-1) * @pagesize) +1) and (@pageindex *@p Agesize)

Now let's write a stored procedure paging

Alter Procedure pagepager  

@TableName varchar,  

@File varchar (1000),---  

@Where varchar (a),---with and connected  

@OrderFile varchar (100),---sort field  

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

@PageSize varchar (a  

)-- @PageIndex varchar--   

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

begin   

Set @OrderFile = ' ID ';  

End  

if (@OrderType, ') = ")  

begin   

Set @OrderType = ' ASC ' End  

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

begin   

Set @File = ' * '  

end   

declare @select varchar (8000)  

Set @select = '  select ' + @File +  ' From   (    select *,row_number () + @OrderFile + ' + @OrderType + ') as ' RowNumber '  + @TableName + '  where 1=1 ' + @Where + '   ) temp where rownumber between ((' + @PageIndex + '-1) * ' + @Page Size + ') +1) and (' + @PageIndex + ' * ' + @PageSize + ') '  
exec (@select)

The above is the entire content of this article, I hope that you learn row_number pagination help.