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 data values 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
 
In Oracle mode from MariaDB 10.3, PREPARE stmt FROM 'SELECT
:1, :2' is used, instead of ?.
 
Permitted Statements
 
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 now 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/