首页 新闻 赞助 找找看

mysql通用分页存储过程问题

0
悬赏园豆:5 [待解决问题]

dropprocedureifexists prc_page_result;
createprocedure prc_page_result (
in currpage      int,
in columns       varchar(500),
in tablename     varchar(500),
in sCondition    varchar(500),
in order_field   varchar(100),
in asc_field     int,
in primary_field varchar(100),
in pagesize      int
)
begin
    
declare sTemp  varchar(1000);
    
declare sSql   varchar(4000);
    
declare sOrder varchar(1000);
    
    
if asc_field =1then
        
set sOrder = concat(' order by ', order_field, ' desc ');
        
set sTemp  ='<(select min';
    
else
        
set sOrder = concat(' order by ', order_field, ' asc ');
        
set sTemp  ='>(select max';
    
endif;
    
    
if currpage =1then
        
if sCondition <>''then
            
set sSql = concat('select ', columns, ' from ', tablename, ' where ');
            
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
        
else
            
set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');
        
endif;
    
else
        
if sCondition <>''then
            
set sSql = concat('select ', columns, ' from ', tablename);
            
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
            
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
            
set sSql = concat(sSql, '', primary_field, ' from ', tablename, sOrder);
            
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
            
set sSql = concat(sSql, ' limit ?');
        
else
            
set sSql = concat('select ', columns, ' from ', tablename);
            
set sSql = concat(sSql, ' where ', primary_field, sTemp);
            
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
            
set sSql = concat(sSql, '', primary_field, ' from ', tablename, sOrder);
            
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
            
set sSql = concat(sSql, ' limit ?');
        
endif;
    
endif;
    
set@iPageSize= pagesize;
    
set@sQuery= sSql;
    
prepare stmt from@sQuery;
    
execute stmt using @iPageSize;
end

 

第一页数据能显示出来,但是后面所有页的数据都查询不出来,怎么回事,求教!

yuxianghappy的主页 yuxianghappy | 菜鸟二级 | 园豆:225
提问于:2013-02-28 11:40
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册