mysql动态sql问题
DELIMITER $$;
DROP PROCEDURE IF EXISTS `web_oa_database`.`search_problem`$$
CREATE PROCEDURE `web_oa_database`.`search_problem` (
in p_id int,
in tm1 varchar(50),
in tm2 varchar(50),
in k_ft varchar(100),
in p_tp varchar(50),
in k_pb varchar(200),
in p_gd varchar(20),
in p_ic varchar(200),
in s_t varchar(30)
)
BEGIN
declare str varchar(2000);
set str='';
if p_id<>0 then
set str=concat(' where data083.pid=',p_id);
end if;
if tm1<>'' then
if str='' then
set str=concat(' where data083.ftm>=',tm1,' and data083.ftm<=',tm2);
else
set str=concat(str,' and data083.ftm>=',tm1,' and data083.ftm<=',tm2);
end if;
end if;
if k_ft<>'' then
if str='' then
set str=concat(' where data083.kft like',' %',k_ft,'%');
else
set str=concat(str,' and data083.kft like',' %',k_ft,'%');
end if;
end if;
if p_tp<>'' then
if str='' then
set str=concat(' where data083.ptp=',p_tp);
else
set str=concat(str,' and data083.ptp=',p_tp);
end if;
end if;
if k_pb<>'' then
if str='' then
set str=concat(' where data083.kpb like', ' %',k_pb,'%');
else
set str=concat(str,' and data083.kpb like',' %',k_pb,'%');
end if;
end if;
if p_gd<>'' then
if str='' then
set str=concat(' where data083.pgd=',p_gd);
else
set str=concat(str,' and data083.pgd=',p_gd);
end if;
end if;
if p_ic<>'' then
if str='' then
set str=concat(' where data083.pic like',' %',p_ic,'%');
else
set str=concat(str,' and data083.pic like',' %',p_ic,'%');
end if;
end if;
if s_t<>'' then
if str='' then
set str=concat(' where data083.st=',s_t);
else
set str=concat(str,' and data083.st=',s_t);
end if;
end if;
set str=concat('select * from data083 ',str);
SET @sql=str;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;$$
//错误提示很明了:DBAccess.execQuery()Unknown column '??“???è??è??' in 'where clause',但是检查了半天,没有发现表列名有误。就怀疑是sql语句拼接出了问题,一时间也找不出来,望高手能看下,指点下错误,不胜感激,谢谢!
[解决办法]