千万数量级分页存储过程

/****** Object:  StoredProcedure [dbo].[Usp_PCustomer_V2_Pagination]    Script Date: 10/16/2015 15:36:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







/*

EXEC dbo.Usp_PCustomer_V2_Pagination 
    @Tables = 'V_PCustomer_V2_GetAuditorList', -- varchar(2000)
    @PrimaryKey = 'auditor_id', -- varchar(500)
    @Sort = NULL, -- varchar(500)
    @CurrentPage = 1, -- int
    @PageSize = 0, -- int
    @Fields = '*', -- varchar(2000)
    @Filter = NULL, -- varchar(3000)
    @Group = NULL, -- varchar(1000)
    @TotalRecord = 0, -- int
    @TotalPage = 0 
    
*/



    
    
/*
***************************************************************    
** 千万数量级分页存储过程 **    
***************************************************************    
参数说明:    
1.Tables :表名称,视图    
2.PrimaryKey :主关键字    
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc    
4.CurrentPage :当前页码    
5.PageSize :分页尺寸    
6.Filter :过滤语句，不带Where     
7.Group :Group语句,不带Group By    
***************************************************************/    
ALTER  PROCEDURE [dbo].[Usp_PCustomer_V2_Pagination] --@Tables='TB_Customer', @PrimaryKey='CustID', @CurrentPage=10000,@PageSize=20    
    (
      @Tables VARCHAR(2000) ,
      @PrimaryKey VARCHAR(500) ,
      @Sort VARCHAR(500) = NULL ,
      @CurrentPage INT = 1 ,
      @PageSize INT = 20 ,
      @Fields VARCHAR(2000) = '*' ,
      @Filter VARCHAR(3000) = NULL ,
      @Group VARCHAR(1000) = NULL ,
      @TotalRecord INT = 0 OUTPUT ,
      @TotalPage INT = 0 OUTPUT
    )
AS /**//*默认排序*/    
    IF @Sort IS NULL
        OR @Sort = ''
        SET @Sort = @PrimaryKey + ' DESC'    
    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 = @Tables
            AND c.name = RTRIM(LTRIM(@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 (1=1)' + @Filter + ' '    
            SET @strSimpleFilter = ' AND (1=1)' + @Filter + ' '    
        END    
    ELSE
        BEGIN    
            SET @strSimpleFilter = ''    
            SET @strFilter = ''    
        END    
    IF @Group IS NOT NULL
        AND @Group != ''
        SET @strGroup = ' GROUP BY ' + @Group + ' '    
    ELSE
        SET @strGroup = ''    
    
    IF @Fields IS NULL
        OR @Fields = ''
        SET @Fields = '*'    
    
    
    DECLARE @Sql_Count NVARCHAR(4000)    
    SET @Sql_Count = '    
SELECT    
  @TotalRecord = COUNT(*)    
FROM    
 ' + @Tables + ' ' + @strFilter    
    
    PRINT @Sql_Count    
    
    EXEC sp_executesql @Sql_Count, N'@TotalRecord int OUTPUT',
        @TotalRecord OUTPUT--计算总记录数 
        
    IF @PageSize > 0
        IF ( @TotalRecord > 0 )
            BEGIN 
             
                SET @TotalPage = CAST(( ( @TotalRecord + @PageSize - 1 )
                                        / @PageSize ) AS INT)
            END
        ELSE
            BEGIN
                SET @TotalPage = 0
            END 
    ELSE
        BEGIN
            SET @TotalPage = 1
        END 
        
    --SELECT  @TotalRecord ,
    --        @TotalPage
    IF @CurrentPage > @TotalPage
        BEGIN 
            EXEC(    
            '         
            SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1 <> 1'     
            )    
			RETURN
        END
    
/**//*执行查询语句*/    
    PRINT ( '    
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 + ' isnull(@SortColumn,'''') ' + @strSimpleFilter
            + ' ' + @strGroup + ' ORDER BY ' + @Sort )    
    --PRINT @SortColumn
    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 + ' isnull(@SortColumn,'''') ' + @strSimpleFilter + ' ' + @strGroup+ ' ORDER BY ' + @Sort    
    )    
    
    
    
  





GO