create procedure Pager
(
@cate VarChar(10),
@startIndex int,
@endIndex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(*) from wp_posts
else
begin
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select id from wp_posts order by id desc
select * from wp_posts O,@indextable t where O.id=t.nid
and t.id between @startIndex and @endIndex order by t.id
end
set nocount off
aspnetpager生成的sqlserver2000的分页存储过程,想转换为mysql可用,麻烦大侠修改下,或者推荐一个好用的mysql分页存储过程。
delimiter ||create procedure Pager (
f_cate VarChar(10),
f_startIndex int,
f_endIndex int,
f_docount bit,
OUT o_rowcount int
)
begin
if (f_docount=1) then
select count(*) from wp_posts;
else
begin
create temporary table indextable(id int unsigned not null auto_increment primary key,nid int);
set o_rowcount = f_endIndex;
insert into indextable(nid) select id from wp_posts order by id desc;
set @stmt = concat('select * from wp_posts O,indextable t where O.id=t.nid and t.id between',
f_startIndex,' and ',f_endIndex,' order by t.id');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
drop table indextable;
set @stmt = NULL;
end;
end if;
end||
delimiter ;