One paging Stored Procedure

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