List:General Discussion« Previous MessageNext Message »
From:syed basha Date:April 13 2009 4:36am
Subject:Re: facing problem with is null in stored procedure
View as plain text  
hai shawn green,

          thanks for ur response.My situation is to generate a production
plan.for this I collect the data from sales orders and compare the stock
availability in rthe warehouse and if the stock is less than the ordered
quantity I plan for production and for this I have to issue the raw material
required for the production by calculating based on bom(bill of material)
details.I am doing this but for every item in the sales oredr I am inserting
the record even though same item is in the another order.I have to update
the quantity i the item is already in the list otherwise i have to insert.

                 please give me solution if u got my situation.

On Sun, Apr 12, 2009 at 1:07 AM, Shawn Green <Shawn.Green@stripped> wrote:

> syed basha wrote:
>> 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
> How do you know your condition is true?
> Could this be part of a transaction that is rolled-back instead of
> committed?
> There are too many unknowns about your situation to make a clear judgement
> on this particular problem.
> --
> Shawn Green, MySQL Senior Support Engineer
> Sun Microsystems, Inc.
> Office: Blountville, TN


facing problem with is null in stored proceduresyed basha9 Apr
  • Re: facing problem with is null in stored procedureShawn Green11 Apr
    • Re: facing problem with is null in stored proceduresyed basha13 Apr