MySQL Execute Immediate Solution

Problem

EXECUTE IMMEDIATE is currently not supported by MySQL for executing dynamic SQL, but with the following solution, dynamic SQL can still be one line. Usually writing dynamic SQL in MySQL requires 3 or 4 steps from beginning to end. They are build the query, PREPARE a statement from the query as a user variable, EXECUTE the statement, and finally DEALLOCATE it.

If EXECUTE IMMEDIATE was supported this

SET @sqlText = _query;
PREPARE stmt FROM @sqlText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

would only be this

EXECUTE IMMEDIATE _query;

Solution

The second method would be much cleaner in code. To accomplish this “one line” method, I made the following Stored Procedure on my databases where dynamic SQL is needed.

CREATE PROCEDURE executeImmediate(
_query MEDIUMTEXT
)
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

SET @sqlText = _query;
PREPARE stmt FROM @sqlText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COMMIT;END~

While EXECUTE IMMEDIATE is still not a keyword, the following line of code will accomplish the same task.

CALL executeImmediate(_query);

This solution will keep code cleaner and more precise while the IMMEDIATE keyword is unavailable.

** As it is written now, the Stored Procedure will get the permissions of the user who defines it. For security purposes, it may make sense for you to use SQL SECURITY INVOKER which will only allow the permissions that the user who calls it has.

{o}
Otreva is a custom software product development company focusing on user experience, responsive web development, & mobile application development.

Interested in seeing what it costs to build an app?

Start Quote See Stats
224 Wyoming Ave. #100
Scranton, PA 18503

You've found the secret footer!

Tweets about @Otreva

Or check us out on:

Shopify Experts
Awwwards