How To have Dynamic SQL in MySQL Stored Procedure

mysql dynamic
Brian Boatright picture Brian Boatright · Oct 10, 2008 · Viewed 126.9k times · Source

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

Answer

TimoSolo picture TimoSolo · Apr 20, 2011

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.

Related questions

MySQL pivot table query with dynamic columns

I'm using the following tables for storing product data: mysql> SELECT * FROM product; +---------------+---------------+--------+ | id | name | description | stock | +---------------+---------------+--------+ | 1 | product1 | first product | 5 | | 2 | product2 | second product| 5 | +---------------+---------------+--------+ mysql> SELECT * FROM product_additional; +-----------------+…

Read More
MYSQL select where field does not contain a certain string character

I use sql to select all my sites URLs from a table. $sql = 'select * from pages where "myURL field"; However I want to be a little more specific with my query. There are a few duplicate links in my table …

Read More
mysql (5.1) > create table with name from a variable

I'm trying to create a table with a name based on the current year and month(2011-09), but MySQL doesn't seem to like this. SET @yyyy_mm=Year(NOW())+'-'+Month(NOW()); CREATE TABLE `survey`.`@yyyy_mm` LIKE `survey`.`interim`; …

Read More