Stored Procedure does not update the table for MySQL

Question

Running this routine on HeidiSQL and it runs with 0 rows affected, even though there should be exactly 1 row affected. both select statements seem to work fine outside this Stored Procedure.

BEGIN
    DECLARE someId INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR 
    select anotherId from tableA 
     where yetAnotherId IN(another select statement);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     

OPEN cur1;

    read_loop: LOOP

    IF done THEN
      LEAVE read_loop;
    END IF;

    FETCH cur1 INTO someId;

    update tableB
    set x = 'hello', y = 'world'
    where something = someId;

    END LOOP;

    CLOSE cur1;
END;

I would like to get an idea what could be wrong with the structure of this routine. It looks to me that even thoughthe cursor should contain 1 entry, It does not.

Thanks

EDIT: It looks like 'someId' was matching a table field with the same name, thus the issue. This has been resolved now.

heidisql is just a GUI...which database engine are you connecting to? SQL Server? MySQL? Other? — ADyson, Jun 08 '18 at 11:12
better also to update the tag then people following the mysql tag will see it. I did it for you. — ADyson, Jun 08 '18 at 11:19

score 0 · Answer 1 · answered Jun 08 '18 at 12:29

Your SP looks good, I have made changes for MySql please try it

BEGIN
DECLARE someId INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR 
select anotherId from tableA 
where yetAnotherId IN(another select statement);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO someId;

update tableB
set x = 'hello', y = 'world'
where something = someId;

IF done THEN

CLOSE cur1;
  LEAVE read_loop;

END IF;

END LOOP read_loop;


END;

Stored Procedure does not update the table for MySQL

1 Answers1