delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
--
sibasha
