mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1; 

The second example is similar, but supplies the text of the statement as a user variable: 

mysql> SET @s = ’SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse’; mysql> PREPARE stmt2 FROM @s; mysql> SET @a = 6; mysql> SET @b = 8; mysql> EXECUTE stmt2 USING @a, @b; +------------+ | hypotenuse | +------------+ | 10 | +------------+ mysql> DEALLOCATE PREPARE stmt2; 

Here is an additional example that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable: 

mysql> USE test; mysql> CREATE TABLE t1 (a INT NOT NULL); mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80); mysql> SET @table = ’t1’; mysql> SET @s = CONCAT(’SELECT * FROM ’, @table); mysql> PREPARE stmt3 FROM @s; mysql> EXECUTE stmt3; +----+ | a | +----+ | 4 | | 8 | | 11 | | 32 | | 80 | +----+ mysql> DEALLOCATE PREPARE stmt3; 

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically. 

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends. 

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count  system variable. To prevent the use of prepared statements, set the value to 0. 

## SQL Syntax Permitted in Prepared Statements 

The following SQL statements can be used as prepared statements: 

ALTER TABLE ALTER USER ANALYZE TABLE 

