SQL Note---Paging

With the use of random thoughts, with the memory.

Mastering SQL statements through practical application.

I. SQL paging

1. First method: Filter with ID greater than how much

SELECT TOP 20
*
FROM dbo. Wms_stock
WHERE (Rk_skucode > (SELECT MAX (Rk_skucode)
From (SELECT TOP 40
*
FROM dbo. Wms_stock
ORDER by Rk_skucode
) T
) )
ORDER by dbo. Wms_stock.rk_skucode

Note: ID is greater than the largest of the first 40, that is, 41 bits after sorting.

2. The second method: use not in to exclude certain ordered IDs

SELECT TOP 20
*
FROM dbo. Wms_stock
WHERE (Pk_stockid not in (SELECT TOP 40
Pk_stockid
FROM dbo. Wms_stock
ORDER by Pk_stockid))
ORDER by dbo. Wms_stock.pk_stockid

Note: This method is retrieved according to the order in which the first 40 rows are not next to the 20 rows.

3. Third method: Using Stored procedures and cursors

CREATE PROCEDURE SqlPager
@sqlstr nvarchar (4000),--query string
@currentpage int,--page n
@pagesize INT--Number of rows per page
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1, @[email protected] Output
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF

Reference:

http://www.itlearner.com/article/3740