Tens of millions of levels of paging stored procedure "Deep sea Collection"

Effect Demo: http://www.cn5135.com/_App/Enterprise/QueryResult.aspx

CREATE PROCEDURE cn5135_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 (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