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

by

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

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Can I create view with parameter in MySQL?
  3. Pass array to MySQL stored routine
  4. Connect By Prior Equivalent for MySQL
  5. How to raise an error within a MySQL function
  6. MySQL stored procedure vs function, which would I use when?
  7. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  8. Looping Over Result Sets in MySQL
  9. MySQL create stored procedure syntax with delimiter
  10. mysql dynamic query in stored procedure
  11. MySQL : transaction within a stored procedure
  12. Writing optional parameters within stored procedures in MySQL? [duplicate]
  13. Dynamic table names in stored procedure function
  14. List of Stored Procedures/Functions Mysql Command Line
  15. passing LIMIT as parameters to MySQL sproc
  16. mysql stored procedure that calls itself recursively
  17. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  18. How can I use transactions in my MySQL stored procedure?
  19. MySQL Stored Procedure Error Handling
  20. MySQL error #2014 – Commands out of sync; you can’t run this command now
  21. MySQL Stored Procedures not working with SELECT (basic question)
  22. How to schedule a stored procedure in MySQL
  23. MySQL : When stored procedure parameter name is the same as table column name
  24. What is dynamic SQL?
  25. How to use Table output from stored MYSQL Procedure
  26. How do I write an SP in phpMyAdmin (MySQL)?
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment