Procedure does not run its loop and not from the syntax error

Asked

Viewed 28 times

1

The PROCEDURE below does not present syntax error, but when running LOOP it does not update the records correctly, only a few are updated.

CREATE PROCEDURE `SP_ATU_ITEM_NF` ()
BEGIN
  DECLARE v_nota_antiga INT default 0; 
  DECLARE v_itm INT default 0;
  DECLARE v_MAXIDNOTA INT default (SELECT MAX(idNota) FROM NFItem);
  DECLARE v_Codigo_Antigo VARCHAR(50);
  DECLARE v_Codigo VARCHAR(50);
  DECLARE v_idNota BIGINT default 0;
  DECLARE v_Item BIGINT default 0;
  DECLARE v_done bool default FALSE;
  DECLARE C CURSOR FOR 
  SELECT Codigo, idNota FROM NFItem ORDER BY Codigo,idNota;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  OPEN C;

  LOOP_ATU_ITEM_NF:LOOP
      FETCH C INTO v_Codigo,v_idNota;
    IF v_nota_antiga <> v_idNota AND v_done = FALSE THEN
      SET v_nota_antiga = v_idNota;
      SET v_itm = 1;
      SET v_Codigo_Antigo = v_Codigo;
      FETCH C INTO v_Codigo,v_idNota;
      UPDATE NFItem SET Item = v_itm WHERE idNota = v_nota_antiga 
                                       AND Codigo = v_Codigo_Antigo;
    ELSE IF v_nota_antiga = v_idNota AND v_done = FALSE THEN
      SET v_Codigo_Antigo = v_Codigo;
      SET v_itm = v_itm +1;
      FETCH C INTO v_Codigo,v_idNota;
      UPDATE NFItem SET Item = v_itm WHERE idNota = v_nota_antiga 
                                       AND Codigo = v_Codigo_Antigo;
    ELSE 
      LEAVE LOOP_ATU_ITEM_NF;
    END IF;
        END IF;
  END LOOP LOOP_ATU_ITEM_NF;
  CLOSE C;
END
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.