MySQL Execute Immediate Solution
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;
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.
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.