How Can I Use Variables for Table Names in MySQL Stored Procedures?

Using Variables for Table Names in MySQL Stored Procedures

When working with MySQL stored procedures, it can be useful to pass a table name as a parameter, allowing for greater flexibility in selecting data from different tables. However, simply specifying the table name as a parameter within the procedure may not work as expected.

To correctly pass a table name into a stored procedure, it's necessary to use dynamic SQL, which allows for the execution of SQL statements constructed during runtime. One way to achieve this is through the use of prepared statements.

Consider the following example:

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 procedure, the incoming TableName parameter is used to construct a dynamic SQL statement (sql_text), which is then prepared and executed. This allows the stored procedure to execute a SELECT query on the specified table at runtime.

Alternatively, the dynamic SQL can be constructed directly within the call to the prepared statement, as seen below:

SET sql_text = CONCAT('select concept_id,concept_name,',@vname,' from enc2.concept a JOIN enc2.ratings b USING(concept_id) where concept_name like (''%',@cname,'%'') and 3 is not null order by 3 asc');

PREPARE stmt FROM sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This approach eliminates the need for an additional variable to hold the SQL statement, simplifying the code.

By using dynamic SQL, we can pass table names as parameters to stored procedures, enabling us to select data from various tables dynamically based on the input parameters.

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