分页查询

 

IF   OBJECT_ID (N ' dbo.p_show ' )  IS   NOT   NULL
     DROP   PROCEDURE  dbo.p_show
GO

/**/ /*--实现分页的通用存储过程

    显示指定表、视图、查询结果的第X页
    对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
    如果视图或查询结果中有主键,不推荐此方法
    如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句

最后更新时间: 2008.01.20
--邹建 2003.09(引用请保留此信息)--*/

/**/ /*--调用示例
EXEC dbo.p_show 
    @QueryStr = N'tb',
    @PageSize = 5,
    @PageCurrent = 3,
    @FdShow = 'id, colid, name',
    @FdOrder = 'colid, name'
select id, colid from tb
order by colid, name


EXEC dbo.p_show 
    @QueryStr = N'
SELECT TOP 100 PERCENT 
    * 
FROM dbo.sysobjects
ORDER BY xtype',
    @PageSize = 5,
    @PageCurrent = 2,
    @FdShow = 'name, xtype',
    @FdOrder = 'xtype, name'
--*/
CREATE   PROC  dbo.p_show
     @QueryStr   nvarchar ( 4000 ),         --  表名、视图名、查询语句
     @PageSize   int = 10 ,                 --  每页的大小(行数)
     @PageCurrent   int = 1 ,                 --  要显示的页
     @FdShow   nvarchar  ( 4000 )  =  N '' ,     --  要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
     @FdOrder   nvarchar  ( 1000 )  =  N ''      --  排序字段列表
AS
SET  NOCOUNT  ON
DECLARE
     @FdName  sysname,     -- 表中的主键或表、临时表中的标识列名
     @Id1  sysname,         -- 开始和结束的记录号
     @Id2  sysname,
     @Obj_ID   int              -- 对象ID

-- 表中有复合主键的处理
DECLARE
     @strfd   nvarchar ( 2000 ),         -- 复合主键列表
     @strjoin   nvarchar ( 4000 ),     -- 连接字段
     @strwhere   nvarchar ( 2000 )     -- 查询条件


SELECT
     @Obj_ID   =   OBJECT_ID ( @QueryStr ),
     @FdShow   =   CASE  
                 WHEN   @FdShow   >  N ''   THEN  N '   '   +   @FdShow
                 ELSE  N '  * '
             END ,
     @FdOrder   =   CASE
                 WHEN   @FdOrder   >  N ''   THEN  N '  ORDER BY  '   +   @FdOrder
                 ELSE  N '   '  
             END ,
     @QueryStr   =   CASE
                 WHEN   @Obj_ID   IS   NULL   THEN  N '  ( '   +   @QueryStr   +  N ' )A '
                 ELSE  N '   '   +   @QueryStr
             END

--  如果显示第一页，可以直接用 top 来完成
IF   @PageCurrent   =   1     
BEGIN
     SELECT  
         @Id1   =   CAST ( @PageSize   as   varchar ( 20 ))
     EXEC (N '
SELECT TOP  '   +   @Id1   +  N '
     '   +   @FdShow   +  N '
FROM  '   +   @QueryStr   +  N '
'   +   @FdOrder
)
     RETURN
END

--  如果是表, 则检查表中是否有标识更或主键
IF   @Obj_ID   IS   NULL   OR   OBJECTPROPERTY ( @Obj_ID ,  ' IsTable ' )  =   0
     GOTO  lb_usetemp
ELSE
BEGIN
     SELECT
         @Id1   =   CAST ( @PageSize   as   varchar ( 20 )),
         @Id2   =   CAST (( @PageCurrent   -   1 )  *   @PageSize   as   varchar ( 20 ))

     --  标识列
     SELECT
         @FdName   =  name
     FROM  dbo.syscolumns
     WHERE  id  =   @Obj_ID
         AND  status  =   0x80
     IF   @@ROWCOUNT   =   0              -- 如果表中无标识列,则检查表中是否有主键
     BEGIN
         DECLARE
             @pk_number   int

         SELECT
             @strfd   =  N '' ,
             @strjoin   =  N '' ,
             @strwhere   =  N ''

         SELECT
             @strfd   =   @strfd  
                     +  N ' , '   +   QUOTENAME (name),
             @strjoin   =   @strjoin  
                     +  N '  AND A. '   +   QUOTENAME (name) 
                     +  N ' =B. '   +    QUOTENAME (name),
             @strwhere   =   @strwhere  
                     +  N '  AND B. '   +   QUOTENAME (name)  +  N '  IS NULL '
         FROM (
             SELECT
                IX.id, IX.indid,
                IXC.colid, ixc.keyno,
                C.name
             FROM  dbo.sysobjects O, 
                dbo.sysindexes IX,
                dbo.sysindexkeys IXC,
                dbo.syscolumns C
             WHERE  O.parent_obj  =   @Obj_ID
                 AND  O.xtype  =   ' PK '
                 AND  O.name  =  IX.name
                 AND  IX.id  =   @Obj_ID
                 AND  IX.id  =  IXC.id
                 AND  IX.indid  =  IXC.indid
                 AND  IXC.id  =  C.id
                 AND  IXC.colid  =  C.colid
        )A
         ORDER   BY  keyno

         SELECT
             @pk_number   =   @@ROWCOUNT ,            
             @strfd   =   STUFF ( @strfd ,  1 ,  1 , N '' ),
             @strjoin   =   STUFF ( @strjoin ,  1 ,  5 , N '' ),
             @strwhere   =   STUFF ( @strwhere ,  1 ,  5 , N '' )            

         IF   @pk_number   =   0
             GOTO  lb_usetemp         -- 如果表中无主键,则用临时表处理
         ELSE   IF   @pk_number   =   1
         BEGIN
             SELECT
                 @FdName   =   @strfd
             GOTO  lb_useidentity     --  使用单一主键
         END
         ELSE
             GOTO  lb_usepk         --  使用复合主键
     END
END

/**/ /*--使用标识列或主键为单一字段的处理方法--*/
lb_useidentity:    
EXEC (N '
SELECT TOP  '   +   @Id1   +  N '
     '   +   @FdShow   +  N '
FROM  ' + @QueryStr   +  N '
WHERE  '   +   @FdName   +   '  NOT IN(
        SELECT TOP  '   +   @Id2   +  N '
             '   +   @FdName   +   '
        FROM  '   +   @QueryStr   +  N '
         '   +   @FdOrder   +  N ' )
'   +   @FdOrder   +  N '
' )
RETURN

/**/ /*--表中有复合主键的处理方法--*/
lb_usepk:        
EXEC (N '
SELECT 
     '   +   @FdShow   +  N '
FROM(
    SELECT TOP  '   +   @Id1   +  N '
        A.*
    FROM  '   +   @QueryStr   +  N '  A
        LEFT JOIN(
                SELECT TOP  '   +   @Id2   +  N '
                     '   +   @strfd   +  N '  
                FROM  '   +   @QueryStr   +  N '
                 '   +   @FdOrder   +  N '
            )B
                ON  '   +   @strjoin   +  N '
    WHERE  '   +   @strwhere   +  N '
     '   +   @FdOrder   +  N '
)A
'   +   @FdOrder   +  N '
' )
RETURN

/**/ /*--用临时表处理的方法--*/
lb_usetemp:        
SELECT
     @FdName   =   QUOTENAME (N ' ID_ '   +   CAST ( NEWID ()  as   varchar ( 40 ))),
     @Id1   =   CAST ( @PageSize   *  ( @PageCurrent - 1 )  as   varchar ( 20 )),
     @Id2   =   CAST ( @PageSize   *   @PageCurrent - 1   as   varchar ( 20 ))

EXEC (N '
SELECT 
     '   +   @FdName   +  N '  = IDENTITY(int, 0, 1),
     '   +   @FdShow   +  N '
INTO #tb
FROM(
    SELECT TOP 100 PERCENT 
        * 
    FROM  '   +   @QueryStr   +  N '
     '   +   @FdOrder   +  N '
)A
'   +   @FdOrder   +  N '

SELECT 
     '   +   @FdShow   +  N '
FROM #tb 
WHERE  '   +   @FdName   +   '  BETWEEN  '   +   @Id1   +   '  AND  '   +   @Id2   +  N '
'
)
GO