MySQL Stored Procedure dynamic change name of table.

Question

Hello, I wanna change dynamicaly name of table in sql query. For example I have next stored procedure:

CREATE PROCEDURE NewProc(IN tableName varchar(64),IN message text)
BEGIN
    INSERT INTO tableName VALUES (message);
END;

I need to change tableName in runtime, Can I to do it or not? Thanks.

Answer 1

You must use dynamic SQL to prepare and execute an SQL string, to achieve what you describe.

Dynamic table names (or column names, or SQL keywords, etc.) must be interpolated into the SQL string before prepare. You can't use query parameters for these dynamic elements.

Be careful to avoid SQL injection vulnerabilities when you interpolate the table name into your SQL query. For example, you should check that the table name exists by looking it up in the information schema.

I agree with the comment from @OMG Ponies -- it's a code smell that you have multiple tables with identical structure such that you want to do the exact same insert to the exact same column. Code smells aren't a guarantee that you've got a bad design, but it's worth considering.