stored procedures - use a variable for table name in mysql sproc

Question

I'm trying to pass a table name into my mysql stored procedure to use this sproc to select off of different tables but it's not working...

this is what I"m trying:

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

I've also tried it w/o the @ sign and that just tells me that TableName doesn't exist...which I know :)

question from:https://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc

Answer 1

It depends on the DBMS, but the notation usually requires Dynamic SQL, and runs into the problem that the return values from the function depend on the inputs when it is executed. This gives the system conniptions. As a general rule (and therefore probably subject to exceptions), DBMS do not allow you to use placeholders (parameters) for structural elements of a query such as table names or column names; they only allow you to specify values such as column values.

Some DBMS do have stored procedure support that will allow you to build up an SQL string and then work with that, using 'prepare' or 'execute immediate' or similar operations. Note, however, that you are suddenly vulnerable to SQL injection attacks - someone who can execute your procedure is then able to control, in part, what SQL gets executed.