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
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
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.
This is a great question, but I don't know the answer. I imagine this is going to have to goto the internals team, but I don't know that they'll be big into this site. In the meantime, I can help you deduce some answers.
For starters I see this:
Which makes me think that that has something to do with it. It's not going to be recompiling SQL if it's not even monitoring the metadata. Which means it's an engine concern.
By the same token, when I read this block I think the same thing (engine):
So all in all I'm not entirely sure why they don't allow it, but I can guess. Sorry that I can't help you more, I'm open to sussing this out some more. Best is to hope for some active MySQL devs once we leave private beta ;)
Largely, it is due to security. The exception for procedures is because the dynamic SQL within procedure can be assigned the security context of the executing user. This means that even though the engine does not know what is going to be executed, it can make certain that the user is permitted to access the referenced object(s).
Beyond that, you can raise the ugly issues of what could happen, were it permissible.