Code for querying stored procedures by tens of millions of pages on SQL server

Source: Internet
Author: User

/*************************************** ***** **************************************** * ******************** 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.