开发学院WEB开发ASP 存储过程分页 阅读

存储过程分页

 2001-02-20 10:16:04 来源:WEB开发网   
核心提示:if exists(select * from sysobjects where ID = object_id("up_TopicList")) drop PRoc up_TopicListgocreate proc up_TopicList @a_ForumID int , @a_intDays
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取下一条记录。

Tags:存储 过程

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接