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

Question

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?

Answer 1

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