Tens of millions of levels of paging stored procedure Code in SQL Server _mssql

Source: Internet
Author: User
Tags rowcount
* *************************************************************** * China worry-free business network tens of millions of pages stored procedures * * * ************************ Parameter description: 1.Tables: Table name, view 2.PrimaryKey: Primary keyword 3.Sort: sort statements, without ORDER by example: NewsID Desc , Orderrows Asc 4.CurrentPage: Current page number 5.PageSize: Page size 6.Filter: Filter statement, no where 7.group:group statement, no Group by copyright: China worry-free Business Network (http: www.cn5135.com) Welcome to try, have any experience please M


CREATE PROCEDURE cn5135_sp_pagination
/*
***************************************************************
* * China worry-free business network tens of millions of pages stored procedures * *
***************************************************************
Parameter description:
1.Tables: Table name, view
2.PrimaryKey: Primary keyword
3.Sort: Sort statements without ORDER by example: NewsID desc,orderrows ASC
4.CurrentPage: Current page number
5.PageSize: Paging Size
6.Filter: Filter statements, without where
7.group:group statement without Group by

Copyright: China worry-free Business Network (http://www.cn5135.com)
Welcome to try, have any experience please mail:xzhijun@gmail.com
Note: Please retain the relevant copyright instructions, thank you! ^_^
***************************************************************/
(
@Tables varchar (1000),
@PrimaryKey varchar (100),
@Sort varchar = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar (1000) = ' * ',
@Filter varchar (1000) = NULL,
@Group varchar (1000) = NULL
)
As

/* Default sort */
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 sort 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 systypes 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 statements. * *
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 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 BY ' + @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.