SQL Server paging stored procedures of tens of millions

With the development of the information age, more and more information systems are used, and more information is available. As the amount of information increases, data presentation, especially reports, is demanding more and more system presentation efficiency, the display of tens of millions of data records must be displayed by page. Ifobject_id (SP_Pagination) isnotnulldropprocSP_Paginati

With the development of the information age, more and more information systems are used, and more information is available. As the amount of information increases, data presentation, especially reports, is demanding more and more system presentation efficiency, the display of tens of millions of data records must be displayed by page. If object_id ('SP _ Pagination ') is not null drop proc SP_Paginati

With the development of the information age, more and more information systems are used, and more information is available. As the amount of information increases, data presentation, especially reports, is demanding more and more system presentation efficiency, the display of tens of millions of data records must be displayed by page.
If object_id ('SP _ Pagination ') is not null
Drop proc SP_Pagination
Go
Create PROCEDURE SP_Pagination
/**//*
**************************************** ***********************
** Tens of millions of paging stored procedures **
**************************************** ***********************
Parameter description:
. Tables: Table Name and view (try to solve the problem)
. PrimaryKey: Primary keyword
. Sort: Sorting statement without Order By such as NewsID Desc and OrderRows Asc
. CurrentPage: Current page number
. PageSize: page size
. Filter: Filter statement without Where
. Group: Group statement without Group
**************************************** ***********************/
(
@ Tables varchar (2000 ),
PrimaryKey varchar (500 ),
@ Sort varchar (500) = NULL,
@ CurrentPage int = 1,
@ PageSize int = 5,
@ Fields varchar (2000) = '*',
@ Filter varchar (1000) = NULL,
@ Group varchar (1000) = NULL
)
AS
/** // * Default sorting */
IF @ Sort is null or @ Sort =''
SET @ Sort = @ PrimaryKey
DECLARE @ SortTable varchar (1000)
DECLARE @ SortName varchar (1000)
DECLARE @ strSortColumn varchar (1000)
DECLARE @ operator char (2)
DECLARE @ type varchar (1000)
DECLARE @ prec int
/** // * Set the sorting statement .*/
If charindex ('desc', @ Sort)> 0
BEGIN
SET @ strSortColumn = REPLACE (@ Sort, 'desc ','')
SET @ operator = '<='
END
ELSE
BEGIN
If charindex ('asc ', @ Sort) = 0
Print '1'
Print REPLACE (@ Sort, 'asc ','')
SET @ strSortColumn = REPLACE (@ Sort, 'asc ','')
Print @ strSortColumn
SET @ operator = '> ='
Print @ operator
END
If charindex ('.', @ strSortColumn)> 0
BEGIN
SET @ SortTable = SUBSTRING (@ strSortColumn, 0, CHARINDEX ('.', @ strSortColumn ))
SET @ SortName = SUBSTRING (@ strSortColumn, CHARINDEX ('.', @ strSortColumn) + 1, LEN (@ strSortColumn ))
END
ELSE
BEGIN
SET @ SortTable = @ Tables
SET @ SortName = @ strSortColumn
Print @ SortTable
Print @ SortName
END
SELECT @ type = t. name, @ prec = c. prec
FROM sysobjects o
JOIN syscolumns c on o. id = c. id
JOIN policypes t on c. xusertype = t. xusertype
WHERE o. name = @ SortTable AND c. name = @ SortName
-- Print @ type
-- Print @ prec
If charindex ('Char ', @ type)> 0
SET @ type = @ type + '(' + CAST (@ prec AS varchar) + ')'
DECLARE @ strPageSize varchar (500)
DECLARE @ strStartRow varchar (500)
DECLARE @ strFilter varchar (1000)
DECLARE @ strSimpleFilter varchar (1000)
DECLARE @ strGroup varchar (1000)
/** // * Default current page */
IF @ CurrentPage <1
SET @ CurrentPage = 1
/** // * Set the paging parameter .*/
SET @ strPageSize = CAST (@ PageSize AS varchar (500 ))
SET @ strStartRow = CAST (@ CurrentPage-1) * @ PageSize + 1) AS varchar (500 ))
/** // * Filter and group statement .*/
IF @ Filter is not null and @ Filter! =''
BEGIN
SET @ strFilter = 'where' + @ Filter +''
SET @ strSimpleFilter = 'and' + @ Filter +''
END
ELSE
BEGIN
SET @ strSimpleFilter =''
SET @ strFilter =''
END
IF @ Group is not null and @ Group! =''
SET @ strGroup = 'group by' + @ GROUP +''
ELSE
SET @ strGroup =''
/* Print @ type
Print @ strStartRow
Print @ strSortColumn
Print @ Tables
Print @ strFilter
Print @ strGroup
Print @ Sort */
/** // * Execute the query statement */
EXEC (
'
DECLARE @ SortColumn '+ @ type +'
Set rowcount '+ @ strStartRow +'
SELECT @ SortColumn = '+ @ strSortColumn + 'from' + @ Tables + @ strFilter + ''+ @ strGroup + 'ORDER BY' + @ Sort +'
Set rowcount '+ @ strPageSize +'
SELECT '+ @ Fields + 'from' + @ Tables + 'where' + @ strSortColumn + @ operator +' @ SortColumn '+ @ strSimpleFilter + ''+ @ strGroup + 'order '+ @ Sort +'
'
)

The following is an example of how to call this stored procedure in the database query analyzer. It is the result set of the Traffic_Sites original Table query.

Take the Traffic_Sites table as an example to execute the paging Stored Procedure Script: exec SP_Pagination 'traffic _ Sites ', 'id', 'Id asc ,'*','',''

Execute the preceding SQL result:

,