CODEWITHSUNDEEP
mysqldynamicprepared-statementsession-variableslocal-variables
ssobczak
ssobczak

Reputation: 1855

Dynamic MySQL with local variables

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?

Upvotes: 3

Views: 9058

Answers (2)

Megha Mittal
Megha Mittal

Reputation: 51

The link above gives a page not found. See here instead : https://dev.mysql.com/doc/refman/5.7/en/prepare.html

The end para clearly states :

" A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope; "

Upvotes: 0

Justin Grant
Justin Grant

Reputation: 46713

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.

Upvotes: 2

Related Questions