图片 24

数据库分页存储过程(7)

复制代码 代码如下:/*
数据库分页存储过程,支持倒序和升序 参数说明: @tablename:为搜索表名
@tablefield:为表的字段,约定为表的主键,
@where:为搜索表名,要显示所有记录请设为”1=1″
@orderby:为搜索结果排序,如orderbyiddesc
@fieldlist:为字段列表,如userid,username @curpage:当前页码
@page_record:每页记录条数 @Sort:排序标识
结果:返回表tablename中满足条件where的第curpage页的page_record条记录,结果按orderby排序
*/ CREATEPROCEDUREproc_CommonPaging @tablenamevarchar(100),
@tablefieldvarchar(20), @wherevarchar(5000), @orderbyvarchar(500),
@fieldlistvarchar(1000), @curpageint, @page_recordint, @sortvarchar(8)
AS BEGIN DECLARE@cmdvarchar(8000) DECLARE@uprecordint
DECLARE@Opvarchar(2)–操作符 DECLARE@max_minvarchar(4)–最大/最小计算
SET@op=” SET@max_min=’MIN’ IF@sort=’asc’ BEGIN SET@Op=”
SET@max_min=’MAX’ END SET@uprecord=@curpage*@page_record IF@curpage=0
SET@cmd=’SELECTTOP’+cast(@page_recordASNVARCHAR)+”+@fieldlist+’FROM’+@tablename+’WHERE’+@where+”+@orderby
ELSE
SET@cmd=’SELECTTOP’+cast(@page_recordASNVARCHAR)+”+@fieldlist+’FROM’+@tablename+’WHERE’+@where+’AND’+@tablefield+’
‘+@op+'(SELECT’+@max_min+'(‘+@tablefield+’)FROM(SELECTTOP’+cast(@uprecordASNVARCHAR)+”+@tablefield+’FROM’+@tablename+’WHERE
‘+@where+”+@orderby+’)ASTmpTbl)AND’+@where+”+@orderby
SET@cmd=@cmd+’;SELECTCOUNT(*)FROM’+@tablename+’WHERE’+@where EXEC(@cmd)
PRINT(@cmd) END GO

效率对比

图片 1图片 2/**//*
图片 3******************************************************************************************
图片 4  过程名称:Common_ColumnType_Pagination
图片 5  过程功能:
图片 6  代码设计:小朱(zsy619@163.com)
图片 7  设计时间:2005-11-3 13:58:26
图片 8******************************************************************************************
图片 9  功能描述:
图片 10
图片 11******************************************************************************************
图片 12  如果您修改了我的程序,请留下修改记录,以便对程序进行维护,谢谢  !!!
图片 13==========================================================================================
图片 14  修改人            修改时间                修改原因
图片 15——————————————————————————————
图片 16
图片 17==========================================================================================
图片 18
图片 19******************************************************************************************
图片 20  备注:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序         
图片 21    查询可以指定页大小、指定查询任意页、指定输出字段列表,返回总页数
图片 22*****************************************************************************************
图片 23*/
图片 24Create Procedure [dbo].[Common_ColumnType_Pagination]
图片 24    @tb         varchar(50), –表名  
图片 24    @col        varchar(50), –按该列来进行分页  
图片 24    @coltype    int,         –@col列的类型,0-数字类型,1-字符类型,2-日期时间类型  
图片 24    @orderby    bit,         –排序,0-顺序,1-倒序  
图片 24    @collist    varchar(800),–要查询出的字段列表,*表示全部字段  
图片 24    @pagesize   int,         –每页记录数  
图片 24    @page       int,         –指定页  
图片 24    @condition  varchar(800),–查询条件  
图片 24    @pages      int OUTPUT   –总页数
图片 24AS
图片 24    Declare @intResult Int
图片 24    Begin Tran
图片 24    —————————————————————–代码设计——————————————————————–
图片 24    DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
图片 24    IF @condition is null or rtrim(@condition)=”
图片 24    BEGIN–没有查询条件  
图片 24        SET @where1=’ WHERE ‘ 
图片 24        SET @where2=’  ‘
图片 24    END
图片 24    ELSE
图片 24    BEGIN–有查询条件  
图片 24        SET @where1=’ WHERE (‘+@condition+’) AND ‘–本来有条件再加上此条件  
图片 24        SET @where2=’ WHERE (‘+@condition+’) ‘–原本没有条件而加上此条件
图片 24    END
图片 24    SET @sql=’SELECT @pages=CEILING((COUNT(*)+0.0)/’+CAST(@pagesize AS varchar)+ ‘) FROM ‘+@tb+@where2
图片 24    EXEC sp_executesql @sql,N’@pages int OUTPUT’,@pages OUTPUT–计算总页数
图片 24    IF @orderby=0  
图片 24        SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+ ‘ FROM ‘+@tb+@where1+@col+’>(SELECT MAX(‘+@col+’) ‘+  ‘ FROM (SELECT TOP ‘+CAST(@pagesize*(@page-1) AS varchar)+’ ‘+          
图片 24         @col+’ FROM ‘+@tb+@where2+’ORDER BY ‘+@col+’) t) ORDER BY ‘+@col
图片 24    ELSE  
图片 24        SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+’ FROM ‘+@tb+@where1+@col+'<(SELECT MIN(‘+@col+’) ‘+ ‘ FROM (SELECT TOP ‘+CAST(@pagesize*(@page-1) AS varchar)+’ ‘+ 
图片 24        @col+’ FROM ‘+@tb+@where2+’ORDER BY ‘+@col+’ DESC) t) ORDER BY ‘+@col+’ DESC’
图片 24    IF @page=1–第一页 
图片 24        SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+’ FROM ‘+@tb+    
图片 24            @where2+’ORDER BY ‘+@col+CASE @orderby WHEN 0 THEN ” ELSE ‘ DESC’ END
图片 24    EXEC(@sql)
图片 24    Set @intResult = @@ROWCOUNT
图片 24    —————————————————————————————————————————————————-
图片 24    If @@Error <> 0
图片 24    Begin
图片 24        RollBack Tran
图片 24        Return -1
图片 24    End
图片 24    Else
图片 24    Begin
图片 24        Commit Tran
图片 24        Return @intResult
图片 24    End
图片 24GO
图片 24

1. 随便找了个网上效率被认为比较高的分页过程

create PROC [dbo].[xp_GetPager_user_dt2]    
@quitdate nvarchar(10)=’2015-01-01′,
@userno nvarchar(10)=”,
@sortfields nvarchar(100)=”,
@pageindex int=1,
@pagesize int=5
AS
begin  –构建执行脚本
declare @sql nvarchar(1800)=”,
        –存储对象
        @tablename NVARCHAR(50)=’ v_pn_users_fromlocal’,
        –返回字段
        @returnfields nvarchar(1000)=”,
        –where 条件
        @where nvarchar(200)=’ where 1=1 ‘,–and abs([Status]) >=
10 and [Status] <> 40  and isvalid<>-1 ,
        –上次查询数量
        @lastcount int =-1          –计算前面查询的数据总数
        set @lastcount=(@pageindex-1)*@pagesize
        if @lastcount<0 set @lastcount=0
        –判断排序字段
        if @sortfields=”
          set @sortfields=’quitdate’
          
 
–*******************************************返回字段设定*****************************************
set
@returnfields=’code,name,fname,email,isvalid,hiredate,hirevalid,quitdate,costcenter,sex,IDCard,PassDate

–*******************************************由条件构建Where***************************************
 
if @quitdate<>’2015-01-01′
   set @where+=’ and quitdate  > ”’+@quitdate+””
if @userno<>”
   set @where+=’ and code = ”’+@userno+””  
 
–*******************************************由条件构建SQL***************************************
set @sql=’ declare @lastmaxid int=0,@total int
–缓存的临时表,并创建检索rowID
if object_id(”tempdb..#t”) is not null
    drop table #tselect row_number() over (order by ‘+@sortfields+’)
rowid, * into #t from ‘+@tablenameset @sql +=
@where–获取按照执行排序,前面查询的数据的最大ID
set @sql +=’ select top ‘+cast(@lastcount as nvarchar)+’ 
@lastmaxid=max(rowid) from #t’
set @sql+= ‘ group by rowid’–获取符合条件的数据总数
set @sql+=’ select @total=count(*) from #t’
–**********************************************构造执行返回结果的SQL*****************************************
set @sql+=’ select ‘
if @pagesize>0
   set @sql+=’top ‘+cast(@pagesize as nvarchar)
   set @sql+=’ rowid ‘
   
   if @returnfields<>”
   begin
        set @sql+=’,’+@returnfields        
   end            
   set @sql+=’ from #t
            where rowid>@lastmaxid order by rowid’set @sql+=’ select
@total total,’+cast(@pagesize as nvarchar)+’ pagesize,’+cast(@pageindex
as nvarchar)+’ pageindex’set @sql+=’ if object_id(”tempdb..#t”) is
not null
    drop table #t’
exec(@sql)  END

2. 优化后的分页过程