How Can I Use a Dynamic Table Name in a MySQL Stored Procedure?

Dynamic Table Name in MySQL Stored Procedure

When working with relational databases, selecting data from various tables is a common requirement. To avoid creating multiple stored procedures for each table, developers often seek ways to dynamically use a variable to represent the table name within a stored procedure.

One approach is to utilize prepared statements. Consider the following stored procedure:

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

Prepared statements allow you to construct dynamic SQL queries by binding variables to placeholders. In this case, the variable TableName is bound to the placeholder in the SQL query. When the stored procedure is executed, the actual table name is substituted into the query, allowing for selection from the specified table.

However, it's important to note that the example you provided attempted to use the variable @TableName directly in the query without binding. This approach is incorrect and would result in a syntax error. To resolve the issue, you must use the technique described above, which involves using prepared statements and explicitly binding the variable using the SET statement.

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