MySQL Bugs: #46741: stored procedure not separated from stored function

Bug #46741	stored procedure not separated from stored function
Submitted:	15 Aug 2009 8:25	Modified:	16 Aug 2009 9:02
Reporter:	Miran Cvenkel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.31-comunity	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	dynamic sql, stored function, stored procedure

Description:
In stored function: dynamic sql is not not allowed in stored function or trigger.

OK, thought that then I can have:
1. function , within it, call procedure having dynamic sql
2. Nope, does not work --> Sql exception 

Mybe this is logical in some way, but not handy, work around is then two separate calls from programm.

How to repeat:
/*this works*/
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `testProc`()
BEGIN

call proc_having_dynamic_sql();

END $$

DELIMITER ;

/*this does not --> -2*/

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `testFunc`() RETURNS int(11)
BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION return -2;

  call proc_having_dynamic_sql();

END $$

DELIMITER ;

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Stored procedure always returns result set, but function is not allowed to output something, it just returns result. Same for triggers. This is why you get such error.

dynamic SQL is not allowed in stored functions and triggers because they are called as a part of another statement.

For every statement MySQL needs to know all affected tables in advance.
Thus, if a trigger or a stored function is involved it should know all tables that this trigger or function may possibly access. Which pretty much means that dynamic SQL should be prohibited.

If a stored procedure is called from a stored function or a trigger it becomes a part of another statement and the same restriction applies - all tables must be known in advance.