How to select from MySQL where Table name is Variable

Question

I have a case where getting the table name should be from a set variable like:

SET @ID_1 = (SELECT ID FROM `slider` LIMIT 0,1);
SET @Cat = (SELECT Category FROM `slider` LIMIT 0,1);
select * from @Cat where ID = @ID_1

but doing that way MySQL outputs an error, so could someone show me how I can achieve that, because these are my baby steps in MySQL.

Solution

You'd have to do this with a prepared statement. Something like:

SET @s = CONCAT('select * from ', @Cat, ' where ID = ', @ID_1); 

PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

Comments

[Rosmarine Popcorn]

One more thing it show resul only when i remove DEALLOCATE PREPARE stm1;

[Tim Visée]

Brilliant solution. Helped me to solve the issue I'm having, thanks.

[Frozen Flame]

What does the DEALLOCATE PREPARE do?

[ruzenhack]

@FrozenFlame, release the stmt1. If you do not release it, may you encounter the max limit of statements, enforced by max_prepared_stmt_count variable system, how you can read here.

[oldboy]

is this still valid syntax in 2018??

[Agamemnus]

I think so... Don't forget, this only works for procedures, not functions.

[Raymond Nijland]

I know this is a old answer but there is one big problem with this is that it opens you up to SQL injections.. EXECUTE <> USING <> syntax is safe for SQL injections.. So you need to execute PREPARE table_exists FROM 'SELECT TABLE_NAME FROM information_schema.TABLE WHERE TABLE_NAME = ?'; EXECUTE table_exists USING @Cat first to check and use that to validate if there is not SQL injection vector in @Cat

[Simon.S.A.]

Welcome to stackoverflow. Please post code as text, not as images. See How to Answer

[Tal Kohavy]

What if I also need to store the result into a variable? SELECT x INTO @x FROM @y.... Is it possible with with CONCAT?