Syntax
------

PREPARE stmt_name FROM preparable_stmt

Description
-----------

The PREPARE statement prepares a statement and assigns it a name, stmt_name,
by which to refer to the statement later. Statement names are not case
sensitive. preparable_stmt is either a string literal or a user variable (not
a local variable, an SQL expression or a subquery) that contains the text of
the statement. The text must represent a single SQL statement, not multiple
statements. Within the statement, "?" characters can be used as parameter
markers to indicate where data values are to be bound to the query later when
you execute it. The "?" characters should not be enclosed within quotes, even
if you intend to bind them to string values. Parameter markers can be used
only where expressions should appear, not for SQL keywords, identifiers, and
so forth.

The scope of a prepared statement is the session within which it is created.
Other sessions cannot see it.

If a prepared statement with the given name already exists, it is deallocated
implicitly before the new statement is prepared. This means that if the new
statement contains an error and cannot be prepared, an error is returned and
no statement with the given name exists.

Prepared statements can be PREPAREd and EXECUTEd in a stored procedure, but
not in a stored function or trigger. Also, even if the statement is PREPAREd
in a procedure, it will not be deallocated when the procedure execution ends.

A prepared statement can access user-defined variables, but not local
variables or procedure's parameters.

If the prepared statement contains a syntax error, PREPARE will fail. As a
side effect, stored procedures can use it to check if a statement is valid.
For example:

CREATE PROCEDURE `test_stmt`(IN sql_text TEXT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT CONCAT(sql_text, ' is not valid');
    END;
    SET @SQL := sql_text;
    PREPARE stmt FROM @SQL;
    DEALLOCATE PREPARE stmt;
END;

The FOUND_ROWS() and ROW_COUNT() functions, if called immediatly after
EXECUTE, return the number of rows read or affected by the prepared
statements; however, if they are called after DEALLOCATE PREPARE, they provide
information about this statement. If the prepared statement produces errors or
warnings, GET DIAGNOSTICS return information about them. DEALLOCATE PREPARE
shouldn't clear the diagnostics area, unless it produces an error.

A prepared statement is executed with EXECUTE and released with DEALLOCATE
PREPARE.

The max_prepared_stmt_count server system variable determines the number of
allowed prepared statements that can be prepared on the server. If it is set
to 0, prepared statements are not allowed. If the limit is reached, an error
similar to the following will be produced:

ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements 
 (current value: 0)

Oracle Mode
-----------

MariaDB starting with 10.3
--------------------------
In Oracle mode from MariaDB 10.3, PREPARE stmt FROM 'SELECT :1, :2' is used,
instead of ?.

Permitted Statements
--------------------

MariaDB starting with 10.6.2
----------------------------
All statements can be prepared, except PREPARE, EXECUTE, and DEALLOCATE / DROP
PREPARE.

Prior to this, not all statements can be prepared. Only the following SQL
commands are permitted:

* ALTER TABLE
* ANALYZE TABLE
* BINLOG
* CACHE INDEX
* CALL
* CHANGE MASTER
* CHECKSUM {TABLE | TABLES}
* COMMIT
* {CREATE | DROP} DATABASE
* {CREATE | DROP} INDEX
* {CREATE | RENAME | DROP} TABLE
* {CREATE | RENAME | DROP} USER
* {CREATE | DROP} VIEW
* DELETE
* DESCRIBE
* DO
* EXPLAIN
* FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS |
STATUS | 
 MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES | QUERY CACHE |
TABLE_STATISTICS | 
 INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS}
* GRANT
* INSERT
* INSTALL {PLUGIN | SONAME}
* HANDLER READ
* KILL
* LOAD INDEX INTO CACHE
* OPTIMIZE TABLE
* REPAIR TABLE
* REPLACE
* RESET {MASTER | SLAVE | QUERY CACHE}
* REVOKE
* ROLLBACK
* SELECT
* SET
* SET GLOBAL SQL_SLAVE_SKIP_COUNTER
* SET ROLE
* SET SQL_LOG_BIN
* SET TRANSACTION ISOLATION LEVEL
* SHOW EXPLAIN
* SHOW {DATABASES | TABLES | OPEN TABLES | TABLE STATUS | COLUMNS | INDEX |
TRIGGERS | 
 EVENTS | GRANTS | CHARACTER SET | COLLATION | ENGINES | PLUGINS [SONAME] |
PRIVILEGES | 
 PROCESSLIST | PROFILE | PROFILES | VARIABLES | STATUS | WARNINGS | ERRORS |
 TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS |
AUTHORS | 
 CONTRIBUTORS}
* SHOW CREATE {DATABASE | TABLE | VIEW | PROCEDURE | FUNCTION | TRIGGER |
EVENT}
* SHOW {FUNCTION | PROCEDURE} CODE
* SHOW BINLOG EVENTS
* SHOW SLAVE HOSTS
* SHOW {MASTER | BINARY} LOGS
* SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
* SLAVE {START | STOP}
* TRUNCATE TABLE
* SHUTDOWN
* UNINSTALL {PLUGIN | SONAME}
* UPDATE

Synonyms are not listed here, but can be used. For example, DESC can be used
instead of DESCRIBE.

Compound statements can be prepared too.

Note that if a statement can be run in a stored routine, it will work even if
it is called by a prepared statement. For example, SIGNAL can't be directly
prepared. However, it is allowed in stored routines. If the x() procedure
contains SIGNAL, you can still prepare and execute the 'CALL x();' prepared
statement.

PREPARE supports most kinds of expressions as well, for example:

PREPARE stmt FROM CONCAT('SELECT * FROM ', table_name);

When PREPARE is used with a statement which is not supported, the following
error is produced:

ERROR 1295 (HY000): This command is not supported in the prepared statement
protocol yet

Example
-------

create table t1 (a int,b char(10));
insert into t1 values (1,"one"),(2, "two"),(3,"three");
prepare test from "select * from t1 where a=?";
set @param=2;
execute test using @param;
+------+------+
| a    | b    |
+------+------+
|    2 | two  |
+------+------+
set @param=3;
execute test using @param;
+------+-------+
| a    | b     |
+------+-------+
|    3 | three |
+------+-------+
deallocate prepare test;

Since identifiers are not permitted as prepared statements parameters,
sometimes it is necessary to dynamically compose an SQL statement. This
technique is called dynamic SQL). The following example shows how to use
dynamic SQL:

CREATE PROCEDURE test.stmt_test(IN tab_name VARCHAR(64))
BEGIN
	SET @sql = CONCAT('SELECT COUNT(*) FROM ', tab_name);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;

CALL test.stmt_test('mysql.user');
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Use of variables in prepared statements:

PREPARE stmt FROM 'SELECT @x;';

SET @x = 1;

EXECUTE stmt;
+------+
| @x   |
+------+
|    1 |
+------+

SET @x = 0;

EXECUTE stmt;
+------+
| @x   |
+------+
|    0 |
+------+

DEALLOCATE PREPARE stmt;

URL: https://mariadb.com/kb/en/prepare-statement/