MYSQL通用存储过程

CREATE DEFINER = 'qtfrad'@'%'
PROCEDURE JiuHeng.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 = 1 then

set sOrder = concat(' order by ', order_field, ' desc ');

else

set sOrder = concat(' order by ', order_field, ' asc ');

end if;
if sCondition = '' then

set sCondition = ' 1=1 ';
end if;

set @count= CONCAT('select count(*) count from ',tablename, ' where ',sCondition);
set @sSql1 = concat('select ', columns, ' from ', tablename, ' where 1=1 and ' , sCondition, sOrder, ' limit ',(currpage-1)*pagesize,',',pagesize);

prepare stmtsql from @count;
execute stmtsql;
prepare stmtsql1 from @sSql1;
execute stmtsql1;

 

 

 

end