千万级分页通用存储过程

【核心代码】

/************************************************************
 * Author:Yutao
 * Time: 2016/8/4 15:20:48
 ************************************************************/

/****************************************************************
 * 
 *     千万数量级分页存储过程
 * 
 * ***************************************************************
 * 
 *  参数说明：
 *  
 *  1.Tables          ：表名称,视图
 *  2.PrimaryKey      ：主关键字段
 *  3.Sort            ：排序(注:不带Order By。如：UserId Desc,UserName Asc)
 *  4.CurrentPage     : 当前页码
 *  5.PageSize        ：分页尺寸
 *  6.Filter          ：过滤语句(注：不带Where。)
 *  7.Group           ：Group语句(注：不带Group By)
 * 
 * 
 ****************************************************************/

ALTER PROCEDURE [dbo].[SP_Pagination]
(
    @Tables          VARCHAR(2000),
    @PrimaryKey      VARCHAR(500),
    @Sort            VARCHAR(500) = NULL,
    @CurrentPage     INT = 1,
    @PageSize        INT = 10,
    @Fields          VARCHAR(2000) = '*',
    @Filter          VARCHAR(1000) = NULL,
    @Group           VARCHAR(1000) = NULL
)
AS
	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
	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)
	IF @CurrentPage < 1
	    SET @CurrentPage = 1
	SET @strPageSize = CAST(@PageSize AS VARCHAR(500))
	SET @strStartRow = CAST(((@CurrentPage - 1) * @PageSize   1) AS VARCHAR(500))
	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 = ''
	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   ''
	      )