How Can I Build and Use Dynamic SQL within MySQL Stored Procedures?

Building Dynamic SQL in MySQL Stored Procedures

Dynamic SQL, introduced in MySQL 5.0.13, allows stored procedures to construct SQL statements at runtime.

How to Build and Use Dynamic SQL in a Stored Procedure:

Create a stored procedure as follows:

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 ;

Explanation:

• Define the @s variable to store the dynamic SQL statement.
• Use the PREPARE statement to prepare the dynamic SQL statement for execution.
• Execute the prepared statement using EXECUTE.
• Deallocate the prepared statement using DEALLOCATE PREPARE.

Example:

To select a specific column from a specified table:

CALL dynamic('employees', 'salary');

Note:

Dynamic SQL is not supported in functions or triggers. For more information, refer to the MySQL documentation.

The above is the detailed content of How Can I Build and Use Dynamic SQL within MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!