CREATE procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
as
if(@docount=1)
begin
select count(id) from luntan where this_id=@this_id
end
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where
this_id=@this_id order by reply_time desc
select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
GO
存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总
数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where
this_id=@this_id order by reply_time desc
真的是妙不可言!!set rowcount @PageUpperBound当记录数达到
@PageUpperBound时就会停止处理查询
,select id 只把id列取出放到临时表里,select a.* from luntan
a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id