How To have Dynamic SQL in MySQL Stored Procedure

Question

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

Answer 1

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.

Answer 2

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!