Dynamic MySQL with local variables

Question

How can I use dynamic SQL statements in MySQL database and without using session variables?

Right now I have such a code (in MySQL stored procedure):

(...)
DECLARE TableName VARCHAR(32);
SET @SelectedId = NULL;
SET @s := CONCAT("SELECT Id INTO @SelectedId FROM ", TableName, " WHERE param=val LIMIT 1");
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF ISNULL(@SelectedId) THEN 
(...)

But I'd like to use only local variables, that means I'd like to start this procedure with:

DECLARE TableName VARCHAR(32);
DECLARE s VARCHAR(1024);
DECLARE SelectedId INTEGER UNSIGNED;
(...)

and do not use @ char anywhere. Is there any way to do this?

Answer 1

Sorry, prepared statements in MySQL are session-global. According to http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html, "A prepared statement is also global to the session."

And there's no other way (besides prepared statements) to execute dynamic SQL in MySQL 5.x.

So you can of course replace "@s" above, but AFAIK you're stuck with @SelectedId.

In MySQL 6.x, there is a feature planned which will add an "EXECUTE IMMEDIATE" statement which will execute dynamic SQL. See http://forge.mysql.com/worklog/task.php?id=2793.