1
一个BBS的分页:
2
3 if exists ( select * from sysobjects where id = object_id ( '''' up_GetTopicList '''' ))
4 drop proc up_GetTopicList
5 go
6
7 create proc up_GetTopicList
8 @a_intForumID int ,
9 @a_intPageNo int ,
10 @a_intPageSize int
11 as
12 /* 定义局部变量 */
13 declare @intBeginID int
14 declare @intEndID int
15 declare @intRootRecordCount int
16 declare @intPageCount int
17 declare @intRowCount int
18 /* 关闭计数 */
19 set nocount on
20
21 /* 检测是否有这个版面 */
22 if not exists ( select * from forum where id = @a_intForumID )
23 return ( - 1 )
24
25 /* 求总共根贴数 */
26 select @intRootRecordCount = count ( * ) from bbs where fatherid = 0 and forumid = @a_intForumID
27 if ( @intRootRecordCount = 0 ) -- 如果没有贴子,则返回零
28 return 0
29
30 /* 判断页数是否正确 */
31 if ( @a_intPageNo - 1 ) * @a_intPageSize > @intRootRecordCount
32 return ( - 1 )
33
34 /* 求开始rootID */
35 set @intRowCount = ( @a_intPageNo - 1 ) * @a_intPageSize + 1
36 /* 限制条数 */
37 set rowcount @intRowCount
38 select @intBeginID = rootid from bbs where fatherid = 0 and forumid = @a_intForumID
39 order by id desc
40
41 /* 结束rootID */
42 set @intRowCount = @a_intPageNo * @a_intPageSize
43 /* 限制条数 */
44 set rowcount @intRowCount
45 select @intEndID = rootid from bbs where fatherid = 0 and forumid = @a_intForumID
46 order by id desc
47
48 /* 恢复系统变量 */
49 set rowcount 0
50 set nocount off
51
52 select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid ,
53 a.rootid ,
54 '''' Bytes '''' = datalength (a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point
55 from bbs as a join BBSUser as b on a.UserID = b.ID
56 where Forumid = @a_intForumID and a.rootid between @intEndID and @intBeginID
57 order by a.rootid desc , a.ordernum desc
58 return ( @@rowcount )
59 -- select @@rowcount
60 go
2
3 if exists ( select * from sysobjects where id = object_id ( '''' up_GetTopicList '''' ))
4 drop proc up_GetTopicList
5 go
6
7 create proc up_GetTopicList
8 @a_intForumID int ,
9 @a_intPageNo int ,
10 @a_intPageSize int
11 as
12 /* 定义局部变量 */
13 declare @intBeginID int
14 declare @intEndID int
15 declare @intRootRecordCount int
16 declare @intPageCount int
17 declare @intRowCount int
18 /* 关闭计数 */
19 set nocount on
20
21 /* 检测是否有这个版面 */
22 if not exists ( select * from forum where id = @a_intForumID )
23 return ( - 1 )
24
25 /* 求总共根贴数 */
26 select @intRootRecordCount = count ( * ) from bbs where fatherid = 0 and forumid = @a_intForumID
27 if ( @intRootRecordCount = 0 ) -- 如果没有贴子,则返回零
28 return 0
29
30 /* 判断页数是否正确 */
31 if ( @a_intPageNo - 1 ) * @a_intPageSize > @intRootRecordCount
32 return ( - 1 )
33
34 /* 求开始rootID */
35 set @intRowCount = ( @a_intPageNo - 1 ) * @a_intPageSize + 1
36 /* 限制条数 */
37 set rowcount @intRowCount
38 select @intBeginID = rootid from bbs where fatherid = 0 and forumid = @a_intForumID
39 order by id desc
40
41 /* 结束rootID */
42 set @intRowCount = @a_intPageNo * @a_intPageSize
43 /* 限制条数 */
44 set rowcount @intRowCount
45 select @intEndID = rootid from bbs where fatherid = 0 and forumid = @a_intForumID
46 order by id desc
47
48 /* 恢复系统变量 */
49 set rowcount 0
50 set nocount off
51
52 select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid ,
53 a.rootid ,
54 '''' Bytes '''' = datalength (a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point
55 from bbs as a join BBSUser as b on a.UserID = b.ID
56 where Forumid = @a_intForumID and a.rootid between @intEndID and @intBeginID
57 order by a.rootid desc , a.ordernum desc
58 return ( @@rowcount )
59 -- select @@rowcount
60 go