一个分页存储过程

来源：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 for
 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