How Can I Use Dynamic SQL in MySQL Stored Procedures?

Using Dynamic SQL in MySQL Stored Procedures

In MySQL versions 5.0.13 and later, dynamic SQL can be implemented within stored procedures.

Building Dynamic SQL Statements:

To build dynamic SQL statements, the CONCAT() function is used to concatenate strings and dynamically create SQL statements. Here's an example:

SET @s = CONCAT('SELECT ',col,' FROM ',tbl );

In the above example, @s would contain the dynamic SQL statement:

SELECT col FROM tbl

Preparing and Executing Dynamic Statements:

Once the dynamic SQL statement is built, it needs to be prepared and executed:

PREPARE stmt FROM @s;
EXECUTE stmt;

Deallocating Prepared Statements:

After execution, the prepared statement must be deallocated to release resources:

DEALLOCATE PREPARE stmt;

Limitations:

Dynamic SQL is supported in stored procedures, but not in functions or triggers.

Example:

The following stored procedure demonstrates 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 ;

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