CODEWITHSUNDEEP
mysqlstored-procedures
Mr. Boy
Mr. Boy

Reputation: 63718

Call a stored procedure for each row returned by a query in MySQL

I want a MySQL stored procedure which effectively does:

foreach id in (SELECT id FROM objects WHERE ... ) CALL testProc(id)

I think I simply want the MySQL answer to this question but I don't understand cursors well: How do I execute a stored procedure once for each row returned by query?

Upvotes: 34

Views: 46517

Answers (1)

eggyal
eggyal

Reputation: 125865

Concepts such as “loops” (for-each, while, etc) and “branching” (if-else, call, etc) are procedural and do not exist in declarative languages like SQL. Usually one can express one’s desired result in a declarative way, which would be the correct way to solve this problem.

For example, if the testProc procedure that is to be called uses the given id as a lookup key into another table, then you could (and should) instead simply JOIN your tables together—for example:

SELECT ...
FROM   objects JOIN other USING (id)
WHERE  ...

Only in the extremely rare situations where your problem cannot be expressed declaratively should you then resort to solving it procedurally instead. Stored procedures are the only way to execute procedural code in MySQL. So you either need to modify your existing sproc so that it performs its current logic within a loop, or else create a new sproc that calls your existing one from within a loop:

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

Upvotes: 50

Related Questions