How Can I Dynamically Select Data from Different Tables Using MySQL Stored Procedures?

Dynamic Table Selection Using Variables in MySQL Stored Procedures

To utilize a stored procedure in MySQL for selecting data from various tables, the table name must be passed as a parameter. However, encountering issues while attempting this can be frustrating.

Let's address the example provided where the following code isn't working as expected:

CREATE PROCEDURE `usp_SelectFromTables`(
 IN TableName varchar(100)
)
BEGIN
        SELECT * FROM @TableName;
END

The error faced when attempting to execute this code is that the TableName does not exist. This is because the variable @TableName isn't being resolved correctly. To rectify this issue, dynamic SQL is employed.

Dynamic SQL allows the execution of SQL statements built at runtime, making it ideal for situations like this. Here's a corrected version of the code using dynamic SQL:

CREATE PROCEDURE `usp_SelectFromTables`(
 IN TableName varchar(100)
)
BEGIN
        SET @sql_text = CONCAT('SELECT * FROM ', TableName);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
END

In this code, the CONCAT() function is used to construct the SQL statement based on the provided table name. The PREPARE statement prepares the dynamic SQL for execution, and the EXECUTE statement executes it. Finally, the DEALLOCATE PREPARE statement deallocates the prepared statement, freeing up resources.

The above is the detailed content of How Can I Dynamically Select Data from Different Tables Using MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!