0

According to this answer Call a stored procedure for each row returned by a query in MySQL described as following:

CREATE PROCEDURE foo() BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id BIGINT UNSIGNED;
  DECLARE cur CURSOR FOR SELECT id FROM objects WHERE ...;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  testLoop: LOOP
    FETCH cur INTO _id;
    IF done THEN
      LEAVE testLoop;
    END IF;
    CALL testProc(_id);
  END LOOP testLoop;

  CLOSE cur;
END

How can i set a variable and increment it over each iteration? The following code does't work and I don't know why.

CREATE PROCEDURE foo() BEGIN
      DECLARE done BOOLEAN DEFAULT FALSE;
      DECLARE _id BIGINT UNSIGNED;
      DECLARE counter INT DEFAULT 0;
      DECLARE cur CURSOR FOR SELECT id FROM objects WHERE ...;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

      OPEN cur;

      testLoop: LOOP
        FETCH cur INTO _id;
        IF done THEN
          LEAVE testLoop;
        END IF;
        CALL testProc(_id);

        counter = counter + 1;

      END LOOP testLoop;

      CLOSE cur;
    END
Community
  • 1
  • 1
Martin
  • 215
  • 5
  • 14
  • possible duplicate of [Return value from MySQL stored procedure](http://stackoverflow.com/questions/4362722/return-value-from-mysql-stored-procedure) –  Dec 26 '14 at 01:48
  • Try `set count = counter + 1` in the loop. – Gordon Linoff Dec 26 '14 at 01:51

0 Answers0