• 学习存储过程的时候参看的两个DVBBS的存储过程和两个初步了解后自己写的存储过程(带分页的)


    <dvbbs7>
    ================================

    ALTER PROCEDURE dv_list

    @boardid int=1,
    @pagenow int=1,  --当前页数            
    @pagesize int=1, --定义每页面帖子数目
    @tl int=0,  --按时间段查询
    @topicmode int=0, --专题
    @totalrec int output


     AS
    set nocount on
    declare @int_topnum int
    declare @int_timenum int
    declare @var_times varchar(5000)

    if @pagenow>1
     if @topicmode>0
     begin
     select @int_timenum=(@pagenow-1)*@pagesize
     set rowcount @int_timenum
     select @var_times=lastposttime from Dv_Topic where boardID=@boardID and istop = 0 and mode=@topicmode ORDER BY lastposttime desc

     set rowcount @pagesize
     select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from dv_topic where boardID=@boardID and istop = 0 and mode=@topicmode and lastposttime < @var_times ORDER BY lastposttime desc

     set nocount off
     return
     end
     else
     begin
     select @int_timenum=(@pagenow-1)*@pagesize
     set rowcount @int_timenum
     select @var_times=lastposttime from Dv_Topic where boardID=@boardID and istop = 0 ORDER BY lastposttime desc

     set rowcount @pagesize
     select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from dv_topic where boardID=@boardID and istop = 0 and lastposttime < @var_times ORDER BY lastposttime desc

     set nocount off
     return
     end

    else
     if @topicmode>0
     begin
     set rowcount @pagesize
     select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from Dv_topic where boardID=@boardid and istop = 0 and mode=@topicmode ORDER BY lastposttime desc
     end
     else
     begin
     set rowcount @pagesize
     select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from Dv_topic where boardID=@boardid and istop = 0 ORDER BY lastposttime desc
     end

    ================================

    ALTER PROCEDURE dv_toplist

    @pagenow int  ,
    @pagesize int ,
    @reture_value int output,
    @intUserRecordCount int output

    as
    /*定义局部变量*/
    declare @intBeginID int
    declare @intEndID int
    declare @intPageCount int
    declare @intRowCount int

    /*关闭计数*/
    set nocount on

    /*求总用户数*/
    select @intUserRecordCount = count(*) from [dv_user]
    if (@intUserRecordCount = 0) --如果没有用户,则返回零
    set @reture_value =0
     
    /*判断页数是否正确*/
    if (@pagenow - 1) * @pagesize > @intUserRecordCount
    set @reture_value =1
    --return (-1)

    /*求开始userID*/
    set @intRowCount = (@pagenow - 1) * @pagesize + 1
    /*限制条数*/
    set rowcount @intRowCount
    select @intBeginID = userid from [dv_user] order by userid desc

    /*结束userID*/
    set @intRowCount = @pagenow * @pagesize
    /*限制条数*/
    set rowcount @intRowCount
    select @intEndID = userid from [dv_user] order by userid desc

    /*恢复系统变量*/
    set rowcount 0
    set nocount off

    select username,useremail,userclass,UserIM,UserPost,JoinDate,userwealth,userid from [dv_user] where userid between @intEndID and @intBeginID order by userid desc
    return(@@rowcount)
    --select @@rowcount


    <zbbs>
    ================================================
    ALTER PROCEDURE zbbs_topic
    @page_size int,
    @page_now int,
    @board int,
    @rsc int output

    AS
    declare @intCount int
    declare @lasttime datetime
    declare @affrow int
    SET NOCOUNT ON

    select @intCount=count(*) from z_topic where board=@board and topicid=0

    if not (@page_size*(@page_now)<@intCount)
    set @page_now=@intCount/@page_size-1

    if @intCount=0
    begin
    set @page_now=0
    set @intCount=1
    end
    set @affrow= @page_now*@page_size+1
    set rowcount @affrow
    select @lasttime=lasttime from z_topic where board=@board and topicid=0 order by lasttime desc
    set rowcount @page_size
    select * from z_topic where board=@board and lasttime<=@lasttime and topicid=0 order by lasttime desc
     
      
     set @rsc=@intCount

     RETURN

    ================================================
    ALTER PROCEDURE zbbs_topic2
    @page_size int,
    @page_now int,
    @board int,
    @rsc int output

    AS
    declare @intBeginID int
    declare @intEndID int
    declare @intRowCount int
    declare @intCount int
    SET NOCOUNT ON

    select @intCount=count(*) from z_topic where board=@board

    if not (@page_size*(@page_now)<@intCount)
    set @page_now=@intCount/@page_size-1

    set @intRowCount=@page_now*@page_size+1
    set rowcount @intRowCount
    select @intBeginID = id from z_topic where board=@board order by id desc

    set @intRowCount=(@page_now+1)*@page_size
    set rowcount @intRowCount
    select @intEndID=id from z_topic where board=@board order by id desc


    set rowcount 0
    set nocount off

    select * from z_topic where board=@board  and (id between @intEndID and @intBeginID) order by lasttime desc
     
     set @rsc=@intCount

     RETURN

  • 相关阅读:
    wgcloud问题处理——被监控的客户机掉线
    WGCLOUD下发指令时候如何屏蔽linux敏感字符
    WGCLOUD左侧菜单如何保持展开状态
    zerotier 实现 minecraft 局域网联机 | 我的世界联机教程 | minecraft联机
    [ UVa 12096 详解] The Set Stack Computer 集合栈计算机 | map、set、vector、stack、宏函数等知识点简单应用
    [ Uva 101 详解 ] the block problem 木块问题
    Python操作数据库基本操作-sqlalchemy
    统计目录下程序源程序的行数之和-Python
    凯撒密码C++实现
    【题解】[AGC036F] Square Constraints
  • 原文地址:https://www.cnblogs.com/ZetaChow/p/2237465.html
Copyright © 2020-2023  润新知