EXECUTE IMMEDIATE was introduced in MariaDB 10.2.3.
 
Syntax
------ 
EXECUTE IMMEDIATE statement
 
Description
----------- 
EXECUTE IMMEDIATE executes a dynamic SQL statement created
on the fly, which can reduce performance overhead.
 
For example:
 
EXECUTE IMMEDIATE 'SELECT 1' 
 
which is shorthand for:
 
prepare stmt from "select 1";
 
execute stmt;
 
deallocate prepare stmt;
 
EXECUTE IMMEDIATE supports complex expressions as prepare
source and parameters:
 
EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', 't1',
' WHERE a=?') USING 5+5;
 
Limitations: subselects and stored function calls are not
supported as a prepare source.
 
The following examples return an error:
 
CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN
'SELECT * FROM t1';
EXECUTE IMMEDIATE f1();
ERROR 1970 (42000): EXECUTE IMMEDIATE does not support
subqueries or stored functions
 
EXECUTE IMMEDIATE (SELECT 'SELECT * FROM t1');
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that 
 corresponds to your MariaDB server version for the right
syntax to use near 
 'SELECT 'SELECT * FROM t1')' at line 1
 
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING f1();
ERROR 1970 (42000): EXECUTE..USING does not support
subqueries or stored functions
 
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING
(SELECT 10);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that 
 corresponds to your MariaDB server version for the right
syntax to use near 
 'SELECT 10)' at line 1
 
One can use a user or an SP variable as a workaround:
 
CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN
'SELECT * FROM t1';
SET @stmt=f1();
EXECUTE IMMEDIATE @stmt;
 
SET @stmt=(SELECT 'SELECT 1');
EXECUTE IMMEDIATE @stmt;
 
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
SET @param=f1();
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING
@param;
 
SET @param=(SELECT 10);
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING
@param;
 
EXECUTE IMMEDIATE supports user variables and SP variables
as OUT parameters
 
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(OUT a INT)
BEGIN
 SET a:= 10;
END;
$$
DELIMITER ;
SET @a=2;
EXECUTE IMMEDIATE 'CALL p1(?)' USING @a;
SELECT @a;
+------+
| @a |
+------+
| 10 |
+------+
 
Similar to PREPARE, EXECUTE IMMEDIATE is allowed in stored
procedures but is not allowed in stored functions.
 
This example uses EXECUTE IMMEDIATE inside a stored
procedure:
 
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
 EXECUTE IMMEDIATE 'SELECT 1';
END;
$$
DELIMITER ;
CALL p1;
+---+
| 1 |
+---+
| 1 |
+---+
 
This script returns an error:
 
DELIMITER $$
CREATE FUNCTION f1() RETURNS INT
BEGIN
 EXECUTE IMMEDIATE 'DO 1';
 RETURN 1;
END;
$$
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored
function or trigger
 
EXECUTE IMMEDIATE can use DEFAULT and IGNORE indicators as
bind parameters:
 
CREATE OR REPLACE TABLE t1 (a INT DEFAULT 10);
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING
DEFAULT;
SELECT * FROM t1;
+------+
| a |
+------+
| 10 |
+------+
 
EXECUTE IMMEDIATE increments the Com_execute_immediate
status variable, as well as the Com_stmt_prepare,
Com_stmt_execute and Com_stmt_close status variables.
 
Note, EXECUTE IMMEDIATE does not increment the
Com_execute_sql status variable. Com_execute_sql is used
only for PREPARE..EXECUTE.
 
This session screenshot demonstrates how EXECUTE IMMEDIATE
affects status variables:
 
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE
VARIABLE_NAME RLIKE 
 ('COM_(EXECUTE|STMT_PREPARE|STMT_EXECUTE|STMT_CLOSE)');
 
+-----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------+----------------+
| COM_EXECUTE_IMMEDIATE | 0 |
| COM_EXECUTE_SQL | 0 |
| COM_STMT_CLOSE | 0 |
| COM_STMT_EXECUTE | 0 |
| COM_STMT_PREPARE | 0 |
+-----------------------+----------------+
 
EXECUTE IMMEDIATE 'SELECT 1';
+---+
| 1 |
+---+
| 1 |
+---+
 
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE
VARIABLE_NAME RLIKE 
 ('COM_(EXECUTE|STMT_PREPARE|STMT_EXECUTE|STMT_CLOSE)');
+-----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------+----------------+
| COM_EXECUTE_IMMEDIATE | 1 |
| COM_EXECUTE_SQL | 0 |
| COM_STMT_CLOSE | 1 |
| COM_STMT_EXECUTE | 1 |
| COM_STMT_PREPARE | 1 |
+-----------------------+----------------+
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/execute-immediate/