PREPARE, EXECUTE and DEALLOCATE statement in MySQL

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.