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, @ f int, @ L int, @ count int, @ Top int search @ top = count (*) from posts as p1 where p1.postid = SELECT MIN (PostID) from PostidId = 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 (postddate) 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 Postid = 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) from posts as p2 where p1.threadid = p2.threadid) and p1.forumid=@forumid and posttype <> 4 declare my_cursor s CROLL CURSOR for SELECT P1.POSTID from Posts as p1 where p1.postid = (Select Min (PostID) from PostId = p2.threadid) and p1.fordid=@forumid and postType <> 4 Order BY SELECT MAX (PostID) from PostidId = P2 WHERE P1.THREADID = P2.THREADID) DESC OPENM_CURSOR IF @ count @ Top <25 and @ Page = 1 Begin Select @ f = 1 Select @ l = @ count end if @ County @ Top> = 25 and @ Page = 1 Begin Select @ f = 1 Select @ l = 25- @ Top end if (@ Page * 25- @ TOP> @PANT) 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 @l from my_cursor @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 Postid = P2 WHERE P1.THREADID = P2.ThreadID Threadid = p2.threadid)) from posts as p1 where p1.postid = (select min "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 Postid = P2.threadid) Desc close my_cursor end return @@ RowCountGo
