Code of the tens of millions of paging stored procedures in sqlserver
Code for querying stored procedures by tens of millions of pages on SQL server
/*************************************** ***** **************************************** * ******************** Parameter description: 1. tables: Table Name, view 2. primaryKey: Primary keyword 3. sort: Sorting statement without Order By such as NewsID Desc and OrderRows Asc 4. currentPage: Current page number 5. pageSize: page size 6. filter: Filter statement without Where 7. group: Group statement, without Group By All Rights Reserved: China carefree Business Network (http://www.cn5135.com) Welcome to try, what experience please M
Create procedure CN5135_SP_Pagination
/*
**************************************** ***********************
** There are tens of millions of paging stored procedures in China carefree business network **
**************************************** ***********************
Parameter description:
1. Tables: Table Name, View
2. PrimaryKey: Primary Key
3. Sort: Sorting statement without Order By such as NewsID Desc and OrderRows Asc
4. CurrentPage: Current page number
5. PageSize: page size
6. Filter: Filter statement without Where
7. Group: Group statement without Group
All Rights Reserved: China carefree Business Network (http://www.cn5135.com)
Welcome to try, what experience please Mail: xzhijun@GMail.com
Note: Please retain the relevant copyright notice for reprinting. Thank you! Pai_^
**************************************** ***********************/
(
@ Tables varchar (1000 ),
PrimaryKey varchar (100 ),
@ Sort varchar (200) = NULL,
@ CurrentPage int = 1,
@ PageSize int = 10,
@ Fields varchar (1000) = '*',
@ Filter varchar (1000) = NULL,
@ Group varchar (1000) = NULL
)
AS
/* Default sorting */
IF @ Sort is null or @ Sort =''
SET @ Sort = @ PrimaryKey
DECLARE @ SortTable varchar (100)
DECLARE @ SortName varchar (100)
DECLARE @ strSortColumn varchar (200)
DECLARE @ operator char (2)
DECLARE @ type varchar (100)
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
SET @ strSortColumn = REPLACE (@ Sort, 'asc ','')
SET @ 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
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
If charindex ('Char ', @ type)> 0
SET @ type = @ type + '(' + CAST (@ prec AS varchar) + ')'
DECLARE @ strPageSize varchar (50)
DECLARE @ strStartRow varchar (50)
DECLARE @ strFilter varchar (1000)
DECLARE @ strSimpleFilter varchar (1000)
DECLARE @ strGroup varchar (1000)
/* Default current page */
IF @ CurrentPage <1
SET @ CurrentPage = 1
/* Set paging parameters .*/
SET @ strPageSize = CAST (@ PageSize AS varchar (50 ))
SET @ strStartRow = CAST (@ CurrentPage-1) * @ PageSize + 1) AS varchar (50 ))
/* 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 =''
/* 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 +'
'
)
GO
