毫秒级百万数据分页存储过程(mssql)

/****** Object:  StoredProcedure [dbo].[up_Page2005]    Script Date: 11/28/2013 17:10:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[up_Page2005]
    (
      @TableName VARCHAR(MAX) ,        --表名                
      @Fields VARCHAR(5000) = '*' ,    --字段名(全部字段为*)                
      @OrderField VARCHAR(5000) ,        --排序字段(必须!支持多字段)                
      @sqlWhere VARCHAR(5000) = NULL ,--条件语句(不用加where)                
      @pageSize INT ,                    --每页多少条记录                
      @pageIndex INT = 1             --指定当前为第几页            
    )
AS 
    BEGIN                
        DECLARE @sql NVARCHAR(MAX);                
        DECLARE @totalRecord INT;                
        DECLARE @TotalPage INT;                        
                
    --计算总记录数                              
        IF ( @SqlWhere = ''
             OR @sqlWhere = NULL
           ) 
            SET @sql = 'select @totalRecord = count(*) from ' + @TableName                
        ELSE 
            SET @sql = 'select @totalRecord = count(*) from ' + @TableName
                + ' where 1=1 ' + @sqlWhere                
                
        EXEC sp_executesql @sql, N'@totalRecord int OUTPUT',
            @totalRecord OUTPUT--计算总记录数           
                         
    --计算总页数                
        SELECT  @TotalPage = CEILING(( @totalRecord + 0.0 ) / @PageSize)                
                
        IF ( @SqlWhere = ''
             OR @sqlWhere = NULL
           ) 
            SET @sql = 'Select top (' + CONVERT(VARCHAR, @pageSize) + ') '
                + @Fields + ' FROM (select ROW_NUMBER() Over(order by '
                + @OrderField + ') as 编号,' + @Fields + ' from ' + @TableName                 
        ELSE 
            SET @sql = 'Select top (' + CONVERT(VARCHAR, @pageSize) + ') '
                + @Fields + ' FROM (select ROW_NUMBER() Over(order by '
                + @OrderField + ') as 编号,' + @Fields + ' from ' + @TableName
                + ' where 1=1 ' + @SqlWhere                    
                       
                
    --处理页数超出范围情况                
        IF @PageIndex <= 0 
            SET @pageIndex = 1                
                    
        IF @pageIndex > @TotalPage 
            SET @pageIndex = @TotalPage                
                
     --处理开始点和结束点                
        DECLARE @StartRecord INT                
        DECLARE @EndRecord INT                
                    
        SET @StartRecord = ( @pageIndex - 1 ) * @PageSize + 1                
        SET @EndRecord = @StartRecord + @pageSize - 1                
                
    --继续合成sql语句                
        SET @Sql = @Sql + ') as t where 编号>=' + CONVERT(VARCHAR, @StartRecord)
            + ' order by 编号'       
        EXEC(@Sql)                    
        IF @@Error <> 0 
            RETURN -1                  
        ELSE 
            SELECT  @totalRecord ,
                    @TotalPage ---返回记录总数,返回总页数                   
    END 

View Code

 

转载于:https://www.cnblogs.com/tewuapple/p/3448128.html