# PREPARE Statement

## Syntax

```sql
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 an expression with the text of the statement. It cannot contain stored function calls or subqueries. 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 `PREPARE` and [EXECUTE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/execute-statement) in a stored procedure, but not in a stored function or trigger. Also, even if the statement is prepared with `PREPARE` in a procedure, it will not be deallocated when the procedure execution ends.

A prepared statement can access [user-defined variables](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/user-defined-variables), but not [local variables](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-variable) 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:

```sql
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()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/found_rows) and [ROW\_COUNT()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/row_count) functions, if called immediately 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](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/get-diagnostics) return information about them. `DEALLOCATE PREPARE` shouldn't clear the [diagnostics area](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/diagnostics-area), unless it produces an error.

A prepared statement is executed with [EXECUTE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/execute-statement) and released with [DEALLOCATE PREPARE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/deallocate-drop-prepare).

The [max\_prepared\_stmt\_count](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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:

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

### Oracle Mode

In [Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle), `PREPARE stmt FROM 'SELECT :1, :2'` is used instead of `?`.

## Permitted Statements

{% hint style="info" %}
The following is valid from MariaDB **10.6.2.**
{% endhint %}

All statements can be prepared, except [PREPARE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/prepare-statement), [EXECUTE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/execute-statement), and [DEALLOCATE / DROP PREPARE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/deallocate-drop-prepare).

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

* [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table)
* [ANALYZE TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/analyze-table)
* [BINLOG](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/binlog)
* [CACHE INDEX](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/cache-index)
* [CALL](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/call)
* [CHANGE MASTER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/change-master-to)
* [CHECKSUM {TABLE | TABLES}](https://mariadb.com/docs/server/reference/sql-statements/table-statements/checksum-table)
* [COMMIT](https://mariadb.com/docs/server/reference/sql-statements/transactions/commit)
* {[CREATE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-database) | [DROP](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-database)} DATABASE
* {[CREATE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index) | [DROP](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-index)} INDEX
* {[CREATE](https://mariadb.com/docs/server/server-usage/tables/create-table) | [RENAME](https://mariadb.com/docs/server/reference/sql-statements/data-definition/rename-table) | [DROP](https://mariadb.com/docs/server/server-usage/tables/drop-table)} TABLE
* {[CREATE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user) | [RENAME](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/rename-user) | [DROP](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/drop-user)} USER
* {[CREATE](https://mariadb.com/docs/server/server-usage/views/create-view) | [DROP](https://mariadb.com/docs/server/server-usage/views/drop-view)} VIEW
* [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete)
* [DESCRIBE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/describe)
* [DO](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/do)
* [EXPLAIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain)
* [FLUSH](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS |\
  MASTER | SLAVE | DES\_KEY\_FILE | USER\_RESOURCES | [QUERY CACHE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush-query-cache) | TABLE\_STATISTICS |\
  INDEX\_STATISTICS | USER\_STATISTICS | CLIENT\_STATISTICS}
* [GRANT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant)
* [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert)
* INSTALL {[PLUGIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/plugin-sql-statements/install-plugin) | [SONAME](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/plugin-sql-statements/install-soname)}
* [HANDLER READ](https://mariadb.com/docs/server/reference/sql-structure/nosql/handler/handler-commands)
* [KILL](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/kill)
* [LOAD INDEX INTO CACHE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-index)
* [OPTIMIZE TABLE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizing-tables/optimize-table)
* [REPAIR TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/repair-table)
* [REPLACE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/replace)
* [RESET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/reset) {[MASTER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/reset-master) | [SLAVE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/reset-replica) | [QUERY CACHE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/reset)}
* [REVOKE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/revoke)
* [ROLLBACK](https://mariadb.com/docs/server/reference/sql-statements/transactions/rollback)
* [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select)
* [SET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set)
* [SET GLOBAL SQL\_SLAVE\_SKIP\_COUNTER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/set-global-sql_slave_skip_counter)
* [SET ROLE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/set-role)
* [SET SQL\_LOG\_BIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-sql_log_bin)
* [SET TRANSACTION ISOLATION LEVEL](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction)
* [SHOW EXPLAIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-explain)
* SHOW {[DATABASES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-databases) | [TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-tables) | [OPEN TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-open-tables) | [TABLE STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-status) | [COLUMNS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-columns) | [INDEX](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index) | [TRIGGERS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-triggers) |[EVENTS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-events) | [GRANTS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-grants) | [CHARACTER SET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-character-set) | [COLLATION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-collation) | [ENGINES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-events) | \[PLUGINS [SONAME](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-plugins)] | [PRIVILEGES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-privileges) |[PROCESSLIST](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist) | [PROFILE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-profile) | [PROFILES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-profiles) | [VARIABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-variables) | [STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-status) | [WARNINGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-warnings) | [ERRORS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-errors) |[TABLE\_STATISTICS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-statistics) | [INDEX\_STATISTICS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index-statistics) | [USER\_STATISTICS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-user-statistics) | [CLIENT\_STATISTICS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-client-statistics) | [AUTHORS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-authors) |[CONTRIBUTORS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-contributors)}
* SHOW CREATE {[DATABASE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-database) | [TABLE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table) | [VIEW](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-view) | [PROCEDURE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-procedure) | [FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-function) | [TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-trigger) | [EVENT](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-event)}
* SHOW {[FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-function-code) | [PROCEDURE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-procedure-code)} CODE
* [SHOW BINLOG EVENTS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-binlog-events)
* [SHOW SLAVE HOSTS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-replica-hosts)
* SHOW {MASTER | BINARY} LOGS
* SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
* SLAVE {[START](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/start-replica) | [STOP](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/stop-replica)}
* [TRUNCATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table)
* [SHUTDOWN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/shutdown)
* UNINSTALL {PLUGIN | SONAME}
* [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update)

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

[Compound statements](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/using-compound-statements-outside-of-stored-programs) 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](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/signal) can't be directly prepared. However, it is allowed in [stored routines](https://mariadb.com/docs/server/server-usage/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:

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

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

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

## Example

```sql
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:

```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:

```sql
PREPARE stmt FROM 'SELECT @x;';

SET @x = 1;

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

SET @x = 0;

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

DEALLOCATE PREPARE stmt;
```

## See Also

* [EXECUTE Statement](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/execute-statement)
* [DEALLOCATE / DROP Prepared Statement](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/deallocate-drop-prepare)
* [EXECUTE IMMEDIATE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/execute-immediate)
* [Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}