drop procedure if exists GetPlanByPage;
create procedure GetPlanByPage
(
m_pageNo int,
m_perPageCnt int,
m_column varchar(1000),
m_table varchar(1000),
m_condition varchar(1000),
m_orderBy varchar(200),
out m_totalPageCnt varchar(50)
)
begin
set @pageCnt=1;
set @limitStart=(m_pageNo -1)*m_perPageCnt;
set @limitEnd=m_perPageCnt;
set @sqlCnt=CONCAT('select count(1) into @pageCnt from ',m_table);
set @sql= CONCAT('select *,case
when plan_state=0 then _utf8 0xE59CA8E8818C
when plan_state=1 then _utf8 0xE7A6BBE8818C
when plan_state=2 then _utf8 0xE5A496E7BC96
end as plan_stateok,(select userinfo_name from hm_userinfo where hm_userinfo.userinfo_id=hm_plan.plan_uid) as username',m_column,' from ',m_table);
if m_condition IS NOT NULL AND m_condition <> '' THEN
set @sql = CONCAT(@sql,' where ',m_condition);
set @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition);
end if;
if m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
set @sql = CONCAT(@sql,' order by ',m_orderBy);
end if;
set @sql=CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd);
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
set m_totalPageCnt=@pageCnt;
PREPARE record from @sql;
EXECUTE record;
DEALLOCATE PREPARE record;
end;
create procedure GetPlanByPage
(
m_pageNo int,
m_perPageCnt int,
m_column varchar(1000),
m_table varchar(1000),
m_condition varchar(1000),
m_orderBy varchar(200),
out m_totalPageCnt varchar(50)
)
begin
set @pageCnt=1;
set @limitStart=(m_pageNo -1)*m_perPageCnt;
set @limitEnd=m_perPageCnt;
set @sqlCnt=CONCAT('select count(1) into @pageCnt from ',m_table);
set @sql= CONCAT('select *,case
when plan_state=0 then _utf8 0xE59CA8E8818C
when plan_state=1 then _utf8 0xE7A6BBE8818C
when plan_state=2 then _utf8 0xE5A496E7BC96
end as plan_stateok,(select userinfo_name from hm_userinfo where hm_userinfo.userinfo_id=hm_plan.plan_uid) as username',m_column,' from ',m_table);
if m_condition IS NOT NULL AND m_condition <> '' THEN
set @sql = CONCAT(@sql,' where ',m_condition);
set @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition);
end if;
if m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
set @sql = CONCAT(@sql,' order by ',m_orderBy);
end if;
set @sql=CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd);
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
set m_totalPageCnt=@pageCnt;
PREPARE record from @sql;
EXECUTE record;
DEALLOCATE PREPARE record;
end;