Two SQL paging Methods: stored procedure and cursor Stored Procedure

The primary key of the table must be the ID column. [ID] int identity)

1. Stored Procedure
Select top page size *
From testtable
Where (id not in
(Select top page size * Page ID
From table
Order by ID ))
Order by ID

2. cursor Stored Procedure
Create procedure sqlpager
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page n
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor ID
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ p1
Set nocount off

 

The use of more than ID and select top paging efficiency is the highest, You Need To splice SQL statements

The paging efficiency of SQL cursor stored procedures is the worst, but the most common