通用sqlserver分页存储过程

通用sqlserver分页存储过程

来自： http://www.cnblogs.com/nzperfect/archive/2007/05/08/738999.html

单主键：

CREATE   PROC  P_viewPage

     /**/ /*
        nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7  QQ:34813284
        敬告：适用于单一主键或存在唯一值列的表或视图
        ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
           
    */

     @TableName   VARCHAR ( 200 ),      -- 表名
     @FieldList   VARCHAR ( 2000 ),     -- 显示列名，如果是全部字段则为*
     @PrimaryKey   VARCHAR ( 100 ),     -- 单一主键或唯一值键
     @Where   VARCHAR ( 2000 ),         -- 查询条件 不含'where'字符，如id>10 and len(userid)>9
     @Order   VARCHAR ( 1000 ),         -- 排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc                                 
                                  -- 注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
     @SortType   INT ,                -- 排序规则 1:正序asc 2:倒序desc 3:多列排序方法
     @RecorderCount   INT ,           -- 记录总数 0:会返回总记录
     @PageSize   INT ,                -- 每页输出的记录数
     @PageIndex   INT ,               -- 当前页数
     @TotalCount   INT  OUTPUT,       -- 记返回总记录
     @TotalPageCount   INT  OUTPUT    -- 返回总页数
AS
     SET  NOCOUNT  ON

     IF   ISNULL ( @TotalCount , '' )  =   ''   SET   @TotalCount   =   0
     SET   @Order   =   RTRIM ( LTRIM ( @Order ))
     SET   @PrimaryKey   =   RTRIM ( LTRIM ( @PrimaryKey ))
     SET   @FieldList   =   REPLACE ( RTRIM ( LTRIM ( @FieldList )), '   ' , '' )

     WHILE   CHARINDEX ( ' ,  ' , @Order )  >   0   OR   CHARINDEX ( '  , ' , @Order )  >   0
     BEGIN
         SET   @Order   =   REPLACE ( @Order , ' ,  ' , ' , ' )
         SET   @Order   =   REPLACE ( @Order , '  , ' , ' , ' )    
     END

     IF   ISNULL ( @TableName , '' )  =   ''   OR   ISNULL ( @FieldList , '' )  =   ''  
         OR   ISNULL ( @PrimaryKey , '' )  =   ''
         OR   @SortType   <   1   OR   @SortType   > 3
         OR   @RecorderCount    <   0   OR   @PageSize   <   0   OR   @PageIndex   <   0         
     BEGIN  
         PRINT ( ' ERR_00 ' )       
         RETURN
     END     

     IF   @SortType   =   3
     BEGIN
         IF  ( UPPER ( RIGHT ( @Order , 4 )) != '  ASC '   AND   UPPER ( RIGHT ( @Order , 5 )) != '  DESC ' )
         BEGIN   PRINT ( ' ERR_02 ' )  RETURN   END
     END

     DECLARE   @new_where1   VARCHAR ( 1000 )
     DECLARE   @new_where2   VARCHAR ( 1000 )
     DECLARE   @new_order1   VARCHAR ( 1000 )   
     DECLARE   @new_order2   VARCHAR ( 1000 )
     DECLARE   @new_order3   VARCHAR ( 1000 )
     DECLARE   @Sql   VARCHAR ( 8000 )
     DECLARE   @SqlCount   NVARCHAR ( 4000 )

     IF   ISNULL ( @where , '' )  =   ''
         BEGIN
             SET   @new_where1   =   '   '
             SET   @new_where2   =   '  WHERE   '
         END
     ELSE
         BEGIN
             SET   @new_where1   =   '  WHERE  '   +   @where  
             SET   @new_where2   =   '  WHERE  '   +   @where   +   '  AND  '
         END

     IF   ISNULL ( @order , '' )  =   ''   OR   @SortType   =   1    OR   @SortType   =   2  
         BEGIN
             IF   @SortType   =   1  
             BEGIN  
                 SET   @new_order1   =   '  ORDER BY  '   +   @PrimaryKey   +   '  ASC '
                 SET   @new_order2   =   '  ORDER BY  '   +   @PrimaryKey   +   '  DESC '
             END
             IF   @SortType   =   2  
             BEGIN  
                 SET   @new_order1   =   '  ORDER BY  '   +   @PrimaryKey   +   '  DESC '
                 SET   @new_order2   =   '  ORDER BY  '   +   @PrimaryKey   +   '  ASC '
             END
         END
     ELSE
         BEGIN
             SET   @new_order1   =   '  ORDER BY  '   +   @Order
         END

     IF   @SortType   =   3   AND    CHARINDEX ( ' , ' + @PrimaryKey + '   ' , ' , ' + @Order ) > 0
         BEGIN
             SET   @new_order1   =   '  ORDER BY  '   +   @Order
             SET   @new_order2   =   @Order   +   ' , '             
             SET   @new_order2   =   REPLACE ( REPLACE ( @new_order2 , ' ASC, ' , ' {ASC}, ' ), ' DESC, ' , ' {DESC}, ' )            
             SET   @new_order2   =   REPLACE ( REPLACE ( @new_order2 , ' {ASC}, ' , ' DESC, ' ), ' {DESC}, ' , ' ASC, ' )
             SET   @new_order2   =   '  ORDER BY  '   +   SUBSTRING ( @new_order2 , 1 , LEN ( @new_order2 ) - 1 )            
             IF   @FieldList   <>   ' * '
                 BEGIN             
                     SET   @new_order3   =   REPLACE ( REPLACE ( @Order   +   ' , ' , ' ASC, ' , ' , ' ), ' DESC, ' , ' , ' )                              
                     SET   @FieldList   =   ' , '   +   @FieldList                     
                     WHILE   CHARINDEX ( ' , ' , @new_order3 ) > 0
                     BEGIN
                         IF   CHARINDEX ( SUBSTRING ( ' , ' + @new_order3 , 1 , CHARINDEX ( ' , ' , @new_order3 )), ' , ' + @FieldList + ' , ' ) > 0
                         BEGIN  
                         SET   @FieldList   =  
                             @FieldList   +   ' , '   +   SUBSTRING ( @new_order3 , 1 , CHARINDEX ( ' , ' , @new_order3 ))                        
                         END
                         SET   @new_order3   =  
                         SUBSTRING ( @new_order3 , CHARINDEX ( ' , ' , @new_order3 ) + 1 , LEN ( @new_order3 ))
                     END
                     SET   @FieldList   =   SUBSTRING ( @FieldList , 2 , LEN ( @FieldList ))                     
                 END             
         END

     SET   @SqlCount   =   ' SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/ '
                     +   CAST ( @PageSize   AS   VARCHAR ) + ' ) FROM  '   +   @TableName   +   @new_where1
    
     IF   @RecorderCount    =   0
         BEGIN
              EXEC  SP_EXECUTESQL  @SqlCount ,N ' @TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT ' ,
                                @TotalCount  OUTPUT, @TotalPageCount  OUTPUT
         END
     ELSE
         BEGIN
              SELECT   @TotalCount   =   @RecorderCount             
         END

     IF   @PageIndex   >   CEILING (( @TotalCount + 0.0 ) / @PageSize )
         BEGIN
             SET   @PageIndex   =    CEILING (( @TotalCount + 0.0 ) / @PageSize )
         END

     IF   @PageIndex   =   1   OR   @PageIndex   >=   CEILING (( @TotalCount + 0.0 ) / @PageSize )
         BEGIN
             IF   @PageIndex   =   1   -- 返回第一页数据
                 BEGIN
                     SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                                +   @TableName   +   @new_where1   +   @new_order1
                 END
             IF   @PageIndex   >=   CEILING (( @TotalCount + 0.0 ) / @PageSize )   -- 返回最后一页数据
                 BEGIN
                     SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM ( '  
                                +   ' SELECT TOP  '   +   STR ( ABS ( @PageSize * @PageIndex - @TotalCount - @PageSize )) 
                                +   '   '   +   @FieldList   +   '  FROM  '
                                +   @TableName   +   @new_where1   +   @new_order2   +   '  ) AS TMP  '
                                +   @new_order1                     
                 END         
         END     
     ELSE
         BEGIN
             IF   @SortType   =   1    -- 仅主键正序排序
                 BEGIN
                     IF   @PageIndex   <=   CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2    -- 正向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                                        +   @TableName   +   @new_where2   +   @PrimaryKey   +   '  >  '
                                        +   ' (SELECT MAX( '   +   @PrimaryKey   +   ' ) FROM (SELECT TOP  '
                                        +   STR ( @PageSize * ( @PageIndex - 1 ))  +   '   '   +   @PrimaryKey  
                                        +   '  FROM  '   +   @TableName
                                        +   @new_where1   +   @new_order1   + '  ) AS TMP)  ' +   @new_order1
                         END
                     ELSE    -- 反向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM ( '  
                                        +   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '  
                                        +   @FieldList   +   '  FROM  '
                                        +   @TableName   +   @new_where2   +   @PrimaryKey   +   '  <  '
                                        +   ' (SELECT MIN( '   +   @PrimaryKey   +   ' ) FROM (SELECT TOP  '
                                        +   STR ( @TotalCount - @PageSize * @PageIndex )  +   '   '   +   @PrimaryKey  
                                        +   '  FROM  '   +   @TableName
                                        +   @new_where1   +   @new_order2   + '  ) AS TMP)  ' +   @new_order2  
                                        +   '  ) AS TMP  '   +   @new_order1
                         END
                 END
             IF   @SortType   =   2    -- 仅主键反序排序
                 BEGIN
                     IF   @PageIndex   <=   CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2    -- 正向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                                        +   @TableName   +   @new_where2   +   @PrimaryKey   +   '  <  '
                                        +   ' (SELECT MIN( '   +   @PrimaryKey   +   ' ) FROM (SELECT TOP  '
                                        +   STR ( @PageSize * ( @PageIndex - 1 ))  +   '   '   +   @PrimaryKey  
                                        + '  FROM  ' +   @TableName
                                        +   @new_where1   +   @new_order1   +   ' ) AS TMP)  ' +   @new_order1                                
                         END  
                     ELSE    -- 反向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM ( '  
                                        +   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '  
                                        +   @FieldList   +   '  FROM  '
                                        +   @TableName   +   @new_where2   +   @PrimaryKey   +   '  >  '
                                        +   ' (SELECT MAX( '   +   @PrimaryKey   +   ' ) FROM (SELECT TOP  '
                                        +   STR ( @TotalCount - @PageSize * @PageIndex )  +   '   '   +   @PrimaryKey  
                                        +   '  FROM  '   +   @TableName
                                        +   @new_where1   +   @new_order2   + '  ) AS TMP)  ' +   @new_order2  
                                        +   '  ) AS TMP  '   +   @new_order1
                         END   
                 END                          
             IF   @SortType   =   3    -- 多列排序，必须包含主键，且放置最后，否则不处理
                 BEGIN
                     IF   CHARINDEX ( ' , '   +   @PrimaryKey   +   '   ' , ' , '   +   @Order )  =   0  
                     BEGIN   PRINT ( ' ERR_02 ' )  RETURN   END
                     IF   @PageIndex   <=   CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2    -- 正向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM (  '
                                        +   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM (  '
                                        +   '  SELECT TOP  '   +   STR ( @PageSize * @PageIndex )  +   '   '   +   @FieldList
                                        +   '  FROM  '   +   @TableName   +   @new_where1   +   @new_order1   +   '  ) AS TMP  '
                                        +   @new_order2   +   '  ) AS TMP  '   +   @new_order1     
                         END
                     ELSE    -- 反向检索
                         BEGIN
                             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM (  '   
                                        +   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM (  '
                                        +   '  SELECT TOP  '   +   STR ( @TotalCount - @PageSize * @PageIndex + @PageSize )  +   '   '   +   @FieldList
                                        +   '  FROM  '   +   @TableName   +   @new_where1   +   @new_order2   +   '  ) AS TMP  '
                                        +   @new_order1   +   '  ) AS TMP  '   +   @new_order1
                         END
                 END
         END
     PRINT ( @Sql )
     EXEC ( @Sql )
GO

联合主键的：

CREATE   PROC  P_public_ViewPage
     /**/ /*
        no_mIss 通用分页存储过程 2007.3.1  QQ:34813284
        适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开)
        调用：
            第一页查询时返回总记录和总页数及第一页记录：
            EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
                'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1,
                @TotalCount OUTPUT,@TotalPageCount OUTPUT
            其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)：
            EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
                'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89,
                @TotalCount OUTPUT,@TotalPageCount OUTPUT
    */

     @TableName   VARCHAR ( 200 ),      -- 表名
     @FieldList   VARCHAR ( 2000 ),     -- 显示列名
     @PrimaryKey   VARCHAR ( 100 ),     -- 单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)
     @Where   VARCHAR ( 1000 ),         -- 查询条件 不含'where'字符
     @Order   VARCHAR ( 1000 ),         -- 排序 不含'order by'字符,用英文,隔开  
     @RecorderCount   INT ,           -- 记录总数 0:会返回总记录
     @PageSize   INT ,                -- 每页输出的记录数
     @PageIndex   INT ,               -- 当前页数
     @TotalCount   INT  OUTPUT,       -- 返回记录总数
     @TotalPageCount   INT  OUTPUT    -- 返回总页数
AS

     SET  NOCOUNT  ON
    
     SET   @FieldList   =   REPLACE ( @FieldList , '   ' , '' )
     IF   @FieldList   =   ' * '  
         BEGIN   SET   @FieldList   =   ' A.* ' END
     ELSE
         BEGIN
             SET   @FieldList   =   ' A. '   +   REPLACE ( @FieldList , ' , ' , ' ,A. ' )
         END
    
     WHILE   CHARINDEX ( ' ,  ' , @Order ) > 0
     BEGIN
         SET   @Order   =   REPLACE ( @Order , ' ,  ' , ' , ' )
     END

     IF   ISNULL ( @TableName , '' )  =   ''   OR   ISNULL ( @PrimaryKey , '' )  =   ''         
         OR   @RecorderCount   <   0   OR   @PageSize   <   0   OR   @PageIndex   <   0
     BEGIN         
         RETURN
     END
 
     DECLARE   @new_where1   VARCHAR ( 1000 )
     DECLARE   @new_where2   VARCHAR ( 1000 )
     DECLARE   @new_where3   VARCHAR ( 1000 )
     DECLARE   @new_where4   VARCHAR ( 1000 )
     DECLARE   @new_order1   VARCHAR ( 1000 )
     DECLARE   @new_order2   VARCHAR ( 1000 )
     DECLARE   @Fields   VARCHAR ( 1000 )
     DECLARE   @Sql   VARCHAR ( 8000 )
     DECLARE   @SqlCount   NVARCHAR ( 4000 )

     SET   @Fields   =   @PrimaryKey   +   ' , '
     SET   @new_where2   =   ''
     SET   @new_where4   =   ''


     IF   ISNULL ( @where , '' )  =   ''
         BEGIN
             SET   @new_where1   =   '   '
             SET   @new_where3   =   '  WHERE  '
         END
     ELSE
         BEGIN             
             SET   @new_where1   =   '  WHERE  '   +   @where   +   '   '
             SET   @new_where3   =   '  WHERE 1=1  '  
                     +   REPLACE ( '  AND  '   +   @where , '  AND  ' , '  AND A. ' ) +   '  AND  '
         END
    
     WHILE   CHARINDEX ( ' , ' , @Fields ) > 0
     BEGIN
         SET   @new_where2   =   @new_where2  
             +   ' A. '   +   LTRIM ( LEFT ( @Fields , CHARINDEX ( ' , ' , @Fields ) - 1 ))
             +   '  = B. '   +   LTRIM ( LEFT ( @Fields , CHARINDEX ( ' , ' , @Fields ) - 1 ))  +   '  AND  '
         SET   @new_where4   =   @new_where4  
             +   ' B. '   +   LTRIM ( LEFT ( @Fields , CHARINDEX ( ' , ' , @Fields ) - 1 ))  +   '  IS NULL AND  '
         SET   @Fields   =   SUBSTRING ( @Fields , CHARINDEX ( ' , ' , @Fields ) + 1 , LEN ( @Fields ))
     END
     SET   @new_where2   =   LEFT ( @new_where2 , LEN ( @new_where2 ) - 4 )
     SET   @new_where4   =   LEFT ( @new_where4 , LEN ( @new_where4 ) - 4 )

     IF   ISNULL ( @order , '' )  =   ''  
         BEGIN
             SET   @new_order1   =   ''
             SET   @new_order2   =   ''
         END
     ELSE
         BEGIN
             SET   @new_order1   =   '  ORDER BY  '   +   @Order
             SET   @new_order2   =   '  ORDER BY  '  
                     +   RIGHT ( REPLACE ( ' , '   +   @Order , ' , ' , ' , A. '  ),
                             LEN ( REPLACE ( ' , '   +   @Order , ' , ' , ' , A. '  )) - 1 )
         END

     SET   @SqlCount   =   ' SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/ '
                     +   CAST ( @PageSize   AS   VARCHAR ) + ' ) FROM  '   +   @TableName  
                     +   '  A  '   +    @new_where1
    
     IF   @RecorderCount   =   0
         BEGIN
              EXEC  SP_EXECUTESQL  @SqlCount ,N ' @TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT ' ,
                                @TotalCount  OUTPUT, @TotalPageCount  OUTPUT
         END
     ELSE
         BEGIN
              SELECT   @TotalCount   =   @RecorderCount             
         END

     IF   @PageIndex   >   CEILING (( @TotalCount + 0.0 ) / @PageSize )
         BEGIN
             SET   @PageIndex   =   CEILING (( @TotalCount + 0.0 ) / @PageSize )
         END
     IF   @PageIndex   =   1
         BEGIN
             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                        +   @TableName   +   '  A ' +   @new_where1   +   @new_order1
         END
     ELSE
         BEGIN
             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                        +   @TableName   +   '  A LEFT JOIN (SELECT TOP  '  
                        +   STR ( @PageSize * ( @PageIndex - 1 )) 
                        +   '   '   +   @PrimaryKey   +   '  FROM  '   +   @TableName   +   @new_where1
                        +   @new_order1   +   '  )B ON  '   +   @new_where2   +   @new_where3  
                        +   @new_where4   +   @new_order2
         END

EXEC ( @Sql )
GO