The occasional SQL Server cursor paging Query

Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go

/*
-- Implement paging using SQL undisclosed stored procedures
The method is simple and efficient. A known problem is that an empty record set needs to be returned.
The solution is to use set recordset = recordset. nextrecordset
To skip the first record set.
This method is provided by J9988 and changed to a stored procedure for convenient calling.
-- Producer build 2004.05 (reference please keep this information )--*/

-- The disadvantage is that two tables are returned. The first table is empty, and the second table has data.

/* -- Call example
Declare @ PageCount int
Exec sp_PageView
@ SQL = 'select * from sysobjects ',
@ PageCurrent = 2,
@ PageCount = @ PageCount out
SELECT @ PageCount
--*/
Create PROC [dbo]. [sp_PageView]
@ SQL ntext, -- the SQL statement to be executed
@ PageCurrent int = 1, -- the page number to be displayed
@ PageSize int = 10, -- size of each page
@ PageCount int OUTPUT -- total number of pages
AS
SET NOCOUNT ON
DECLARE @ p1 int
-- Initialize the paging cursor
EXEC sp_cursoropen
@ Cursor = @ p1 OUTPUT,
@ Stmt = @ SQL,
@ Scrolopt = 1,
@ Ccopt = 1,
@ Rowcount = @ PageCount OUTPUT

-- Calculate the total number of pages
If isnull (@ PageSize, 0) <1
SET @ PageSize = 10
SET @ PageCount = (@ PageCount + @ PageSize-1)/@ PageSize
If isnull (@ PageCurrent, 0) <1 or isnull (@ PageCurrent, 0)> @ PageCount
SET @ PageCurrent = 1
ELSE
SET @ PageCurrent = (@ PageCurrent-1) * @ PageSize + 1

-- Display data on a specified page
EXEC sp_cursorfetch @ p1, 16, @ PageCurrent, @ PageSize
 
-- Disable paging cursor
EXEC sp_cursorclose @ p1