最近用的一个分页存储过程!
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
2004-7-6 23:22 by zzjjmm