Dynamic SQL in MySQL stored routines According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And why lift it for procedures, but not functions or triggers?
Just hearing the question makes me think of two aspects:

ASPECT #1 : Functions are supposed to be DETERMINISTIC

If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.

Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.

What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.

Notice that MySQL added this option in /etc/my.cnf

log-bin-trust-function-creators


Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.

ASPECT #2 : Triggers should be able to be rolled back


Could you imagine a trigger with all the same behaviors as a function and then introducing Dynamic SQL into the mix?
Could you imagine trying to apply MVCC (Multiversion Concurrecy Control) against Dynamic SQL after applying MVCC to the base table the trigger was meant for?


You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.

In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.

So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.