精彩的近乎完美的分页存储过程

create procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
as
if(@docount=1)
begin
select count(id) from luntan where [email protected]_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 @[email protected][email protected]
set rowcount @pageupperbound
insert into @indextable(nid) select id from luntan where [email protected]_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<[email protected] order by t.id
end
go

存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @pageupperbound
insert into @indextable(nid) select id from luntan where [email protected]_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<[email protected] order by 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)
declare @strsortcolumn varchar(200)
declare @operator char(2)
declare @type varchar(100)
declare @prec int

/*set sorting variables.*/
if charindex('desc',@sort)>0
 begin
  set @strsortcolumn = replace(@sort, 'desc', '')
  set @operator = '<='
 end
else
 begin
  if charindex('asc', @sort) = 0
   set @strsortcolumn = replace(@sort, 'asc', '')
  set @operator = '>='
 end

if charindex('.', @strsortcolumn) > 0
 begin
  set @sorttable = substring(@strsortcolumn, 0, charindex('.',@strsortcolumn))
  set @sortname = substring(@strsortcolumn, charindex('.',@strsortcolumn) + 1, len(@strsortcolumn))
 end
else
 begin
  set @sorttable = @tables
  set @sortname = @strsortcolumn
 end

select @type=t.name, @prec=c.prec
from sysobjects o
join syscolumns c on o.id=c.id
join systypes t on c.xusertype=t.xusertype
where o.name = @sorttable and c.name = @sortname

if charindex('char', @type) > 0
   set @type = @type + '(' + cast(@prec as varchar) + ')'

declare @strpagesize varchar(50)
declare @strstartrow varchar(50)
declare @strfilter varchar(1000)
declare @strsimplefilter varchar(1000)
declare @strgroup varchar(1000)

/*default page number*/
if @pagenumber < 1
 set @pagenumber = 1

/*set paging variables.*/
set @strpagesize = cast(@pagesize as varchar(50))
set @strstartrow = cast(((@pagenumber - 1)*@pagesize + 1) as varchar(50))

/*set filter & group variables.*/
if @filter is not null and @filter != ''
 begin
  set @strfilter = ' where ' + @filter + ' '
  set @strsimplefilter = ' and ' + @filter + ' '
 end
else
 begin
  set @strsimplefilter = ''
  set @strfilter = ''
 end
if @group is not null and @group != ''
 set @strgroup = ' group by ' + @group + ' '
else
 set @strgroup = ''
 
/*execute dynamic query*/
exec(
'
declare @sortcolumn ' + @type + '
set rowcount ' + @strstartrow + '
select @sortcolumn=' + @strsortcolumn + ' from ' + @tables + @strfilter + ' ' + @strgroup + ' order by ' + @sort + '
set rowcount ' + @strpagesize + '
select ' + @fields + ' from ' + @tables + ' where ' + @strsortcolumn + @operator + ' @sortcolumn ' + @strsimplefilter + ' ' + @strgroup + ' order by ' + @sort + '
'
)
go