(@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.idend
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而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!
create procedure paging_rowcount
(
@tables varchar(1000),
@pk varchar(100),
@sort varchar(200) = null,
@pagenumber int = 1,
@pagesize int = 10,
@fields varchar(1000) = '*',
@filter varchar(1000) = null,
@group varchar(1000) = null)
as
/*default sorting*/
if @sort is null or @sort = ''
set @sort = @pk
/*find the @pk type*/
declare @sorttable varchar(100)
declare @sortname varchar(100)
http://www.bkjia.com/phpjc/631236.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/631236.htmltecharticlecreate 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...