论坛交流
首页办公自动化| 网页制作| 平面设计| 动画制作| 数据库开发| 程序设计| 全部视频教程
应用视频: Windows | Word2007 | Excel2007 | PowerPoint2007 | Dreamweaver 8 | Fireworks 8 | Flash 8 | Photoshop cs | CorelDraw 12
编程视频: C语言视频教程 | HTML | Div+Css布局 | Javascript | Access数据库 | Asp | Sql Server数据库Asp.net  | Flash AS
当前位置 > 文字教程 > asp教程
Tag:入门,文摘,实例,技巧,iis,表单,对象,上传,数据库,记录集,session,cookies,存储过程,注入,分页,安全,优化,xmlhttp,fso,jmail,application,防盗链,stream,组件,md5,乱码,缓存,加密,验证码,算法,ubb,正则表达式,水印,,日志,压缩,url重写,控件,函数,破解,触发器,socket,ADO,初学,聊天室,留言本,视频教程

存储过程分页

文章类别:asp | 发表日期:2008-10-5 20:52:09

if exists(select * from sysobjects where ID = object_id("up_TopicList"))
   drop proc up_TopicList
go

create proc up_TopicList
            @a_ForumID int , @a_intDays int , @a_intPageNo int , @a_intPageSize tinyint
   as
       declare @m_intRecordNumber int
       declare @m_intStartRecord  int
       select @m_intRecordNumber = @a_intPageSize * @a_intPageNo
       select @m_intStartRecord = @a_intPageSize * (@a_intPageNo - 1) + 1

       if @a_intDays = 0                      --如果不限定天数
          begin
                /*求符合条件记录数*/
                select "RecordCount" = count(*)                         
                       from BBS where Layer=1 and ForumID = @a_ForumID

               /*输出纪录*/
               /*首先定义可滚动光标*/
               set rowcount @m_intRecordNumber
               declare m_curTemp Scroll cursor
                       for
                          select a.ID ,a.Title , d.UserName , a.FaceID ,
                                'ContentSize' = datalength(a.Content) ,
                                'TotalChilds' = (select sum(TotalChilds)
                                                        from BBS as b
                                                        where a.RootID = b.RootID) ,
                                'LastReplyTime' = (select max(PostTime)
                                                          from BBS as c
                                                          where a.RootID = c.RootID)
                                from BBS as a
                                     join BBSUser as d on a.UserID = d.ID
                                where Layer=1 and ForumID = @a_ForumID
                                order by RootID desc , Layer , PostTime
               open m_curTemp
               fetch absolute @m_intStartRecord from m_curTemp
               while  @@fetch_status = 0
                      fetch next from m_curTemp

               set rowcount 0
               /*清场*/       
               CLOSE m_curTemp
               DEALLOCATE m_curTemp
          end                      
                          
       else                                --如果限定天数          

          begin
                /*求符合条件记录数*/
                select "RecordCount" = count(*)                         
                       from BBS where Layer=1 and ForumID = @a_ForumID
                                      and dateadd(day , @a_intDays , PostTime) > getdate()

               /*输出纪录*/
               /*首先定义可滚动光标*/
               set rowcount @m_intRecordNumber
               declare m_curTemp Scroll cursor
                       for
                          select a.ID ,a.Title , d.UserName , a.FaceID ,
                                'ContentSize' = datalength(a.Content) ,
                                'TotalChilds' = (select sum(TotalChilds)
                                                        from BBS as b
                                                        where a.RootID = b.RootID) ,
                                'LastReplyTime' = (select max(PostTime)
                                                          from BBS as c
                                                          where a.RootID = c.RootID)
                                from BBS as a
                                     join BBSUser as d on a.UserID = d.ID
                                where Layer=1 and ForumID = @a_ForumID
                                      and dateadd(day , @a_intDays , PostTime) > getdate()
                                order by RootID desc , Layer , PostTime
               open m_curTemp
               fetch absolute @m_intStartRecord from m_curTemp
               while  @@fetch_status = 0
                      fetch next from m_curTemp

               set rowcount 0
               /*清场*/       
               CLOSE m_curTemp
               DEALLOCATE m_curTemp
          end                                                
go


注:若在asp中调用存储过程的command对象为cm,则set rs=cm.execute,然后用set rs=rs.nextrecordset取下一条记录。

上一篇:{技巧}友情连接浏览器 人气:4444
下一篇:{技巧}结束ADOVB.INC的办法 人气:4078
视频教程列表
文章教程搜索
 
Asp推荐教程
Asp热门教程
看全部视频教程
购买方式/价格
购买视频教程: 咨询客服
tel:15972130058