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

Copy Code code as follows:

Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go

Create PROCEDURE [dbo]. [Sp_pagination]
/**//*
***************************************************************
* * Tens of millions of levels of paging 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
***************************************************************/
(
@Tables varchar (2000),
@PrimaryKey varchar (500),
@Sort varchar = NULL,
@CurrentPage int = 1,
@PageSize int,
@Fields varchar (2000) = ' * ',
@Filter varchar (1000) = NULL,
@Group varchar (1000) = NULL
)
As
/**//* default Sort */
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
/**//* to 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 (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
/**//* sets the paging parameter. * *
SET @strPageSize = CAST (@PageSize as varchar (500))
SET @strStartRow = CAST ((@CurrentPage-1) * @PageSize + 1) as varchar (500))
/**//* filtering and Grouping 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 + '
'
)