(原创)千万数据级通用分页存储过程

兼容SQL Server2000/2005/2008/2014

直接贴代码出来

 

/*
=============================================
Author:		 chenlong
Create date: 2015-09-01
Description: 千万级分页存储过程
			 经测试11439088记录.每页15条.第8页.执行0.14s
=============================================
*/
ALTER PROCEDURE [dbo].[OPPO_Pagination]
    (
      @TableName VARCHAR(MAX) ,
      @FieldStr NVARCHAR(MAX) = '*' ,
      @OrderByFeild VARCHAR(500) = '' ,
      @WhereStr NVARCHAR(MAX) = '' ,
      @PageIndex INT = 1 ,
      @PageSize INT = 10 ,
      @RecordCount INT OUTPUT
    )
AS
    BEGIN
        SET NOCOUNT ON
        DECLARE @Sql NVARCHAR(MAX) = ''
        DECLARE @Order VARCHAR(500)= ''
 
        SET @RecordCount = 0 
        DECLARE @Where NVARCHAR(MAX) = ''
        IF @WhereStr <> ''
            BEGIN	
                SET @Where = ' WHERE ' + @WhereStr 
            END	
        SET @Sql = @Sql + ' SELECT @Totals=COUNT(1) FROM (SELECT 1 One FROM '
            + @TableName + ' ' + @Where + ') T'
            
        EXEC sp_executesql @Sql, N'@Totals INT OUT', @RecordCount OUT
		
        IF @OrderByFeild <> ''
            BEGIN
                SET @Order = ' ORDER BY ' + @OrderByFeild 
            END
        IF @PageIndex = 1
            BEGIN
                IF ( @WhereStr <> '' )
                    BEGIN
                        SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' '
                            + @FieldStr + ' FROM ' + @TableName + ' WHERE '
                            + @WhereStr + @Order 
                    END
                ELSE
                    BEGIN
                        SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' '
                            + @FieldStr + ' FROM ' + @TableName + ' ' + @Order 
                    END
            END
        ELSE
            BEGIN
                SET @Sql = 'SELECT TOP ' + STR(@PageIndex * @PageSize)
                    + ' IDENTITY(INT,1,1) AS TmpIID, ' + @FieldStr
                    + ' INTO #tmpRecord FROM ' + @TableName 
                IF ( @WhereStr <> '' )
                    BEGIN
                        SET @Sql = @Sql + ' WHERE ' + @WhereStr + @Order 
                    END
                ELSE
                    BEGIN
                        SET @Sql = @Sql + @Order 
                    END
                SET @Sql = @Sql
                    + ' SELECT * FROM #tmpRecord WITH(NOLOCK) WHERE TmpIID > '
                    + STR(( @PageIndex - 1 ) * @PageSize)
                    + ' DROP TABLE #tmpRecord'
            END
 
        PRINT @Sql 
        EXEC (@Sql)
        SET NOCOUNT OFF
    END