这是一个国内出名的论坛的SQL存储过程脚本,是2005.04.07发布的新版本。
【请不要转贴到别处】
– 存储过程名称:dv_Dispbbs
—————————————————————————————————-
- CREATE PROCEDURE [dv_Dispbbs]
- @boardid int=1,
- @pagenow int=1, --当前页数
- @pagesize int=1, --定义每页面帖子数目
- @Announceid int=1,
- @TotalUseTable nvarchar(10) ='Dv_bbs1'
- with encryption
- AS
- set nocount on
- Select AnnounceID from [Dv_bbs1] Where BoardID=@BoardID And
- RootID=@Announceid Order By AnnounceID
—————————————————————————————————-
– 存储过程名称:dv_list
—————————————————————————————————-
- CREATE PROCEDURE [dv_list]
- @boardid int=1,
- @pagenow int=1, --当前页数
- @pagesize int=1, --定义每页面帖子数目
- @tl int=0, --按时间段查询
- @topicmode int=0, --专题
- @totalrec int output
- with encryption
- 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,GetMoney,GetMoneyType,UseTools,IsSmsTopic 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,GetMoney,GetMoneyType,UseTools,IsSmsTopic 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,GetMoney,GetMoneyType,UseTools,IsSmsTopic 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,GetMoney,GetMoneyType,UseTools,IsSmsTopic from Dv_topic
- where boardID=@boardid and istop = 0 ORDER BY lastposttime desc
- end
—————————————————————————————————-
– 存储过程名称:Dv_loadSetup
—————————————————————————————————-
- CREATE PROCEDURE [Dv_loadSetup]
- with encryption
- AS
- Select * from [Dv_setup]
—————————————————————————————————-
– 存储过程名称:dv_toplist
—————————————————————————————————-
- CREATE PROCEDURE [dv_toplist]
- @pagenow int ,
- @pagesize int ,
- @reture_value int output,
- @intUserRecordCount int output
- with encryption
- 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
—————————————————————————————————-
以下说说这个新版与上一版本的脚本区别
1、删除了原来的Dv_TSQL,增加了dv_Dispbbs过程。
2、在Dv_List过程中,有四处TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,
lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,
GetMoney,GetMoneyType,UseTools,IsSmsTopic from
红色为新增。