How to CREATE EVENT from Stored Procedure with a random event name?

Question

I want to EXEC from MySQL Stored Procedure a CREATE EVENT sql.

• I cannot use PREPARE because CREATE EVENT is not allowed in PREPARE, and I want to use a random name, as I need to create a unique event, and I need to pass some date params in the DO section.
• MySQL doesn't have EXEC.

the SQL is built like this:

SET @sql=CONCAT('CREATE EVENT myevent',md5(concat(@dt1,@dt2)),' ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 0 SECOND
                 DO
                    call genRoutes(''',@dt1,''',''',@dt2,''');
                 ');

The generated @sql query looks like:

CREATE EVENT myeventcadb1e41722fb3c9b2e6992e740d76ca ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 0 SECOND
DO
   call genRoutes('2011-04-07 00:00:00','2011-04-14 00:00:00');

Server version 5.1.31-community

How to do this in Stored Procedure?

Answer 1

If you can't do it directly, perhaps you could store impending calls to genRoutes (or whatever) in a table and use a single event to manage the next call. Any time you add to the table of impending calls, if the new call is sooner than the soonest in the table, you could call alter the event to trigger at the new time. When the event triggers, call all of them that are ready, delete them from the table, and alter the event to trigger at the next soonest call.

Alternatively, you could just have an event that triggers ever hour, 15 minutes, 5 minutes (or whatever) and poll the table for ready calls.