How To have Dynamic SQL in MySQL Stored Procedure

Question

How do you build and use dynamic sql in a MySQL stored procedure?

Solution 1

I don't believe MySQL supports dynamic sql. You can do "prepared" statements which is similar, but different.

Here is an example:

mysql> PREPARE stmt FROM 
    -> 'select count(*) 
    -> from information_schema.schemata 
    -> where schema_name = ? or schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt 
    -> USING @schema1,@schema2
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;

The prepared statements are often used to see an execution plan for a given query. Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql.

Here is a good link about this:

Don't forget to deallocate the stmt using the last line!

Good Luck!

Solution 2

After 5.0.13, in stored procedures, you can use dynamic SQL:

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT ',col,' FROM ',tbl );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
delimiter ;

Dynamic SQL does not work in functions or triggers. See the MySQL documentation for more uses.

Solution 3

You can pass thru outside the dynamic statement using User-Defined Variables

Server version: 5.6.25-log MySQL Community Server (GPL)

mysql> PREPARE stmt FROM 'select "AAAA" into @a';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 1 row affected (0.01 sec)

DEALLOCATE prepare stmt;
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
|AAAA  |
+------+
1 row in set (0.01 sec)

Comments

[Jader Dias]

it isn't working when the ? is in the place of the table name

[Jason Stevenson]

Are you trying this via the mysql console? or using another method, if you are using this in code, your provider would need to support it.

[Álvaro González]

Apparently, they're studying EXECUTE IMMEDIATE for future versions.

[Stefan Rogin]

@JaderDias use CONCAT to create the stmt, and only use ? for non-meta information. e.g. : PREPARE stmt FROM CONCAT('SELECT ',col,' FROM ',tbl,' WHERE col = ? AND col2 = ?' );

[Danny Beckett]

Also doesn't work on column names (as with table names) - see below answer/above comment instead.

[Zohar]

Notice that you can only execute one statment each time. View stackoverflow.com/a/20374657/2630035 for an easier way