两个Sql Server 2000的通用分页存储过程

第一个支持唯一主键，第二支持多主键，测试过，效率一般

CREATE   PROC  P_viewPage
     /*
        no_mIss 分页存储过程 2007.2.20  QQ:34813284
        适用于单一主键或存在唯一值列的表或视图          
     */

     @TableName   VARCHAR ( 200 ),      -- 表名
     @FieldList   VARCHAR ( 2000 ),     -- 显示列名
     @PrimaryKey   VARCHAR ( 100 ),     -- 单一主键或唯一值键
     @Where   VARCHAR ( 1000 ),         -- 查询条件 不含'where'字符
     @Order   VARCHAR ( 1000 ),         -- 排序 不含'order by'字符，如id asc,userid 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 ( @TableName , '' )  =   ''   OR   ISNULL ( @FieldList , '' )  =   ''  
         OR   ISNULL ( @PrimaryKey , '' )  =   ''
         OR   @SortType   <   1   OR   @SortType   > 3
         OR   @RecorderCount   <   0   OR   @PageSize   <   0   OR   @PageIndex   <   0
     BEGIN         
         RETURN
     END
 
     DECLARE   @new_where1   VARCHAR ( 1000 )
     DECLARE   @new_where2   VARCHAR ( 1000 )
     DECLARE   @new_order   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   SET   @new_order   =   '  ORDER BY  '   +   @PrimaryKey   +   '  ASC '
             IF   @SortType   =   2   SET   @new_order   =   '  ORDER BY  '   +   @PrimaryKey   +   '  DESC '
         END
     ELSE
         BEGIN
             SET   @new_order   =   '  ORDER BY  '   +   @Order
         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
         BEGIN
             SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '  
                        +   @TableName   +   @new_where1   +   @new_order
         END
     ELSE
         BEGIN
             IF   @SortType   =   1
                 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_order   + '  ) AS TMP)  ' +   @new_order
                 END
             IF   @SortType   =   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_order   +   ' ) AS TMP)  ' +   @new_order                                
                 END        
             IF   @SortType   =   3
                 BEGIN
                     IF   CHARINDEX ( ' , ' , @Order )  =   0   BEGIN   RETURN   END
                     SET   @Sql   =   ' SELECT TOP  '   +   STR ( @PageSize )  +   '   '   +   @FieldList   +   '  FROM  '
                                +   @TableName   +   @new_where2   +   @PrimaryKey   +   '  NOT IN (SELECT TOP  '
                                +   STR ( @PageSize * ( @PageIndex - 1 ))  +   '   '   +   @PrimaryKey
                                +   '  FROM  '   +   @TableName   +   @new_where1   +   @new_order   +   ' ) '
                                +   @new_order
                 END
         END
     EXEC ( @Sql )
GO

 

CREATE   PROC  P_public_ViewPage_per
     /*
        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