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