CREATE PROCEDURE updeditQuantity(p_qty int, p_contractmfgid int, p_rawmaterialid int)
BEGIN
DECLARE c_id, c_qty INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR
SELECT id, quantity
FROM inventory_rawmaterial_contractmfg
WHERE contractmfgid =p_contractmfgid AND rawmaterialid=p_rawmaterialid
ORDER BY quantity;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO c_id, c_qty;
IF done or (p_qty = 0) THEN
LEAVE read_loop;
END IF;
IF (p_qty >= c_qty) then
set p_qty = p_qty - c_qty;
update inventory_rawmaterial_contractmfg set quantity=0,dateupdated=now() where id=c_id;
ELSE
update inventory_rawmaterial_contractmfg set quantity=quantity+p_qty,dateupdated=now() where id=c_id;
set p_qty = 0;
END IF;
END LOOP;
END
Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>
Our community of experts have been thoroughly vetted for their expertise and industry experience.