MySQL PREPARE, EXECUTE and DEALLOCATE PREPARE Statements


Example

PREPARE prepares a statement for execution

EXECUTE executes a prepared statement

DEALLOCATE PREPARE releases a prepared statement

SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6;
SET @b = 8;
EXECUTE stmt2 USING @a, @b;

Result:

+------------+
| hypotenuse |
+------------+
|         10 |
+------------+

Finally,

DEALLOCATE PREPARE stmt2;

Notes:

  • You must use @variables, not DECLAREd variables for FROM @s
  • A primary use for Prepare, etc, is to 'construct' a query for situations where binding will not work, such as inserting the table name.