BEGIN #Routine body goes here... declare v_selectSql varchar(255); #数据查询语句 set @v_selectSql = "select a.id,a.userId,a.name,a.isDefault,a.status,a.addDate,a.updateDate,a.adminStatus,b.name as 'userName' from job_resume as a left join job_basicinfo as b on a.userId=b.userId where 1=1"; if I_name is not null && length(I_name)>0 then set @v_selectSql = concat(@v_selectSql," and a.name like '%",I_name,"%'"); end if; if I_userName is not null && length(I_userName)>0 then set @v_selectSql = concat(@v_selectSql," and b.name like '%",I_userName,"%'"); end if; if I_isOpen is not null then set @v_selectSql = concat(@v_selectSql," and a.status=",I_isOpen); end if; if I_isDefault is not null then set @v_selectSql = concat(@v_selectSql," and a.isDefault=",I_isDefault); end if; if I_addDateBegin is not null && length(I_addDateBegin)>0 then set @v_selectSql = concat(@v_selectSql," and a.addDate>='",I_addDateBegin,"'"); end if; if I_addDateEnd is not null && length(I_addDateEnd)>0 then set @v_selectSql = concat(@v_selectSql," and a.addDate<='",I_addDateEnd,"'"); end if; if I_updateDateBegin is not null && length(I_updateDateBegin)>0 then set @v_selectSql = concat(@v_selectSql," and a.updateDate>='",I_updateDateBegin,"'"); end if; if I_updateDateEnd is not null && length(I_updateDateEnd)>0 then set @v_selectSql = concat(@v_selectSql," and a.updateDate<='",I_updateDateEnd,"'"); end if; if I_adminStatus is not null then set @v_selectSql = concat(@v_selectSql," and a.adminStatus=",I_adminStatus); end if; set @v_selectSql = concat(@v_selectSql," order by a.updateDate desc limit ",I_firstResult,",",I_maxResult); prepare pre_stmt from @v_selectSql; execute pre_stmt; END
BEGIN
#Routine body goes here...
declare v_selectSql varchar(255); #数据查询语句
set @v_selectSql = "select a.id,a.userId,a.name,a.isDefault,a.status,a.addDate,a.updateDate,a.adminStatus,b.name as 'userName' from job_resume as a left join job_basicinfo as b on a.userId=b.userId where 1=1";
if I_name is not null && length(I_name)>0 then
set @v_selectSql = concat(@v_selectSql," and a.name like '%",I_name,"%'");
end if;
if I_userName is not null && length(I_userName)>0 then
set @v_selectSql = concat(@v_selectSql," and b.name like '%",I_userName,"%'");
end if;
if I_isOpen is not null then
set @v_selectSql = concat(@v_selectSql," and a.status=",I_isOpen);
end if;
if I_isDefault is not null then
set @v_selectSql = concat(@v_selectSql," and a.isDefault=",I_isDefault);
end if;
if I_addDateBegin is not null && length(I_addDateBegin)>0 then
set @v_selectSql = concat(@v_selectSql," and a.addDate>='",I_addDateBegin,"'");
end if;
if I_addDateEnd is not null && length(I_addDateEnd)>0 then
set @v_selectSql = concat(@v_selectSql," and a.addDate<='",I_addDateEnd,"'");
end if;
if I_updateDateBegin is not null && length(I_updateDateBegin)>0 then
set @v_selectSql = concat(@v_selectSql," and a.updateDate>='",I_updateDateBegin,"'");
end if;
if I_updateDateEnd is not null && length(I_updateDateEnd)>0 then
set @v_selectSql = concat(@v_selectSql," and a.updateDate<='",I_updateDateEnd,"'");
end if;
if I_adminStatus is not null then
set @v_selectSql = concat(@v_selectSql," and a.adminStatus=",I_adminStatus);
end if;
set @v_selectSql = concat(@v_selectSql," order by a.updateDate desc limit ",I_firstResult,",",I_maxResult);
prepare pre_stmt from @v_selectSql;
execute pre_stmt;
END