SQL Data Paging Solution

Pagination | resolution | Data A lot of friends who start to learn programming when using a database to customize pagination, they encounter problems with writing data paging stored procedures.

Here I have my own little experience and learning experiences. Provide several stored procedures for pagination within the database and share it with you.

1. Use Top
1.1 Paging using the current record number (currentnote) and the paging page size (pagesize)








Create proc GetNextPageInfo2

@pagesize int,--page size

@currentnote int--current Note

As








declare @sql nvarchar (200)








Set @sql =n ' select top ' +convert (varchar), @pagesize) +

' * FROM dbo. Userinfo where userid > ' + CONVERT (varchar), @currentnote)

EXEC sp_executesql @sql

Go








1.2 Page numbering and paging page size with page

Create proc Getnextpageinfo

@pagesize int,--page size

@page int--currentpage NO

As








DECLARE @jilu bigint

Set @jilu = (@pagesize * @page)








declare @sql nvarchar (200)








Set @sql =n ' select top ' +convert (varchar), @pagesize) +

' * FROM dbo. Userinfo where UserID not in (select top ' + CONVERT (varchar), @jilu) +

' UserID from Userinfo ORDER by UserID ' ORDER by UserID '

EXEC sp_executesql @sql

Go

Comparison of two ways: the first execution efficiency should be higher than the second, but the second method is relatively simple to use when the ID number is changeable.








2. Use ROWCOUNT

3.2.1 Paging with current record number (Currentnote) and Paging page size (pagesize)

Create proc GetNextPageInfo3

@pagesize int,----paging size

@currentnote int----The last record number of the current page

As

SET ROWCOUNT @pagesize








SELECT * from UserInfo where userid> @currentnote

















There are, of course, many other ways to do this, except for the above several external causes. Hope that we can learn in the process of a lot of exchanges, we are better together.