Syntax for Prepare, execute statement like dynamic query
PREPARE prepares a statement for execution
EXECUTE executes a prepared statement
DEALLOCATE PREPARE releases a prepared statement
Example of using Prepare Statement
SET @s = 'SELECT ? + ? AS sumtable';
PREPARE stmt1 FROM @s;
SET @a = 4;
SET @b = 6;
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;
Execute:
mysql> SET @s = 'SELECT ? + ? AS sumtable';
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt1 FROM @s;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> SET @a = 4;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @b = 6;
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt1 USING @a, @b;
+----------+
| sumtable |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)
Example of Prepare statement for execute DDL Statement:
SET @stmt ='ALTER TABLE test ADD COLUMN upd_date date';
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;