Source: http://dotnet.mblogger.cn/zzjjmm/posts/1603.aspx
Create procedure DBO. postgetpostbypage
(
@ Page int,
@ Forumid int,
@ Topornot int
)
As
/* Set nocount on */
Declare @ begin int, @ end int, @ F int, @ l int, @ count int, @ top int
Select @ Top = count (*) from posts as P1 where p1.postid = (select Min (postid) from posts as P2 where p1.threadid = p2.threadid)
And p1.forumid = @ forumid and posttype = 4
If @ topornot = 1
Select p1.posttype, p1.title, p1.username, p1.totalviews, p1.postid, p1.threadid, p1.forumid, filename,
Reply = (select count (*) from posts as P2 where p1.threadid = p2.threadid)-1,
Lastdate = (select max (postdate) from posts as P2 where p1.threadid = p2.threadid ),
Lastwriter = (select username from posts as P2 where p2.postid = (select max (postid) from posts as P2 where p1.threadid = p2.threadid ))
From posts as P1 where p1.postid = (select Min (postid) from posts as P2 where p1.threadid = p2.threadid)
And p1.forumid = @ forumid and posttype = 4
Else if @ topornot = 2
Begin
Select @ COUNT = count (*) from posts as P1 where p1.postid = (select Min (postid) from posts as P2 where p1.threadid = p2.threadid)
And p1.forumid = @ forumid and posttype <> 4
Declare my_cursor scroll cursor
Select p1.postid from posts as P1 where p1.postid = (select Min (postid) from posts as P2 where p1.threadid = p2.threadid)
And p1.forumid = @ forumid and posttype <> 4 order by (select max (postid) from posts as P2 where p1.threadid = p2.threadid) DESC
Open my_cursor
If @ count + @ top <25 and @ page = 1
Begin
Select @ F = 1
Select @ l = @ count
End
If @ count + @ top> = 25 and @ page = 1
Begin
Select @ F = 1
Select @ l = 25-@ top
End
If (@ page * 25-@ top> @ count) and @ page> 1
Begin
Select @ F = (@ page-1) * 25 + 1-@ top
Select @ l = @ count
End
If (@ page * 25-@ top <= @ count) and @ page> 1
Begin
Select @ F = (@ page-1) * 25 + 1-@ top
Select @ l = @ page * 25-@ top
End
Fetch absolute @ F from my_cursor into @ begin
Fetch absolute @ l from my_cursor into @ end
Set nocount off
Select p1.posttype, p1.title, p1.username, p1.totalviews, p1.postid, p1.threadid, p1.forumid, filename,
Reply = (select count (*) from posts as P2 where p1.threadid = p2.threadid)-1,
Lastdate = (select max (postdate) from posts as P2 where p1.threadid = p2.threadid ),
Lastwriter = (select username from posts as P2 where p2.postid = (select max (postid) from posts as P2 where p1.threadid = p2.threadid ))
From posts as P1 where p1.postid = (select Min (postid) from posts as P2 where p1.threadid = p2.threadid)
And p1.forumid = @ forumid and postid <= @ begin and postid >=@ end and posttype <> 4 order by (select max (postid) from posts as P2 where p1.threadid = p2.threadid) DESC
Close my_cursor
End
Return @ rowcount
Go