How To have Dynamic SQL in MySQL Stored Procedure

by

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.

More Related Contents:

  1. MySQL pivot table query with dynamic columns
  2. what will be the sql query code of this “Healthcare” database?
  3. Rank function in MySQL
  4. Inserting multiple rows in mysql
  5. How do I quickly rename a MySQL database (change schema name)?
  6. Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
  7. How to display UTF-8 characters in phpMyAdmin?
  8. convert_tz returns null
  9. Convert MySQL to SQlite [closed]
  10. MySQL query String contains
  11. MySQL autoincrement column jumps by 10- why?
  12. Best data type for storing currency values in a MySQL database
  13. ERROR 1067 (42000): Invalid default value for ‘created_at’
  14. Mysql localhost != 127.0.0.1?
  15. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  16. MySQL Workbench: How to keep the connection alive
  17. Capitalize first letter. MySQL
  18. #1055 – Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
  19. Use a trigger to stop an insert or update
  20. mysql query show multiple tables from one ID column
  21. MySQL: ALTER IGNORE TABLE gives “Integrity constraint violation”
  22. MySQL remote connection fails with “unknown authentication method”
  23. Import MySQL database into a SQL Server
  24. How does MySQL’s ORDER BY RAND() work?
  25. How create json format with group-concat mysql?
  26. What is the difference between SQL and MySQL? [closed]
  27. how to use a like with a join in sql?
  28. Escaping the colon character ‘:’ in JPA queries
  29. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  30. Count duplicates records in Mysql table?

Leave a Comment