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 :)

Answer 1

SET @cname:='jello'; SET @vname:='dwb'; 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; 

Answer 2

An extra bit that caused me problems.

I wanted to set the table name and field dynamically in a query as @kyle asked, but I also wanted to store the result of that query into a variable @a within the query.

Instead of putting the variable @a into the concat literally, you need to include it as part of the string text.

delimiter //  CREATE PROCEDURE removeProcessed(table_name VARCHAR(255), keyField VARCHAR(255), maxId INT, num_rows INT)  BEGIN   SET @table_name = table_name;   SET @keyField = keyField;   SET @maxId = maxId;   SET @num_rows = num_rows;    SET @sql_text1 = concat('SELECT MIN(',@keyField,') INTO @a FROM ',@table_name);   PREPARE stmt1 FROM @sql_text1;   EXECUTE stmt1;   DEALLOCATE PREPARE stmt1;    loop_label:  LOOP     SET @sql_text2 = concat('SELECT ',@keyField,' INTO @z FROM ',@table_name,' WHERE ',@keyField,' >= ',@a,' ORDER BY ',@keyField,' LIMIT ',@num_rows,',1');     PREPARE stmt2 FROM @sql_text2;     EXECUTE stmt2;     DEALLOCATE PREPARE stmt2;      ...Additional looping code...      END LOOP; END //  delimiter ; 

So in @sql_text1 assign the result of the query to @a within the string using:

') INTO @a FROM '

Then in @sql_text2 use @a as an actual variable:

,' WHERE ',@keyField,' >= ',@a,' ORDER BY '