CREATE procedure main_table_pwqzc (@pagesize int, --每页行数 @pageindex int, --查询第几页 @docount bit, --是否返回记录数 @this_id int --查询条件(条件简单了点) ) 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) --查询页的ID临时表 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 -- 原存储缺陷,应该加一句 set rowcount 0 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
set rowcount ...
即将过时
(@pagesize int, --每页行数
@pageindex int, --查询第几页
@docount bit, --是否返回记录数
@this_id int --查询条件(条件简单了点)
)
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) --查询页的ID临时表
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
-- 原存储缺陷,应该加一句 set rowcount 0
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