Avatar of Zolf
Zolf
Flag for United Arab Emirates

asked on 

Stored Procedure help

Hi there,

I have this SP and want to understand the logic as to how it is working. i will appreciate if someone can explain it.

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

Open in new window

SQLMicrosoft SQL ServerMySQL Server

Avatar of undefined
Last Comment
theGhost_k8

8/22/2022 - Mon