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

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