troubleshooting Question

Stored Procedure help

Avatar of zolf
zolfFlag for United Arab Emirates asked on
Microsoft SQL ServerMySQL ServerSQL
7 Comments3 Solutions26 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros