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.

score 91 · Accepted Answer · answered Jan 10 '12 at 20:25

91

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;

answered Jan 10 '12 at 20:25

Joe Stefanelli

One more thing it show resul only when i remove DEALLOCATE PREPARE stm1; – Rosmarine Popcorn Jan 10 '12 at 20:37
1

Brilliant solution. Helped me to solve the issue I'm having, thanks. – Tim Visée May 26 '16 at 12:36
What does the `DEALLOCATE PREPARE` do? – Frozen Flame Jun 29 '16 at 02:59
1

@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](https://dev.mysql.com/doc/refman/5.7/en/deallocate-prepare.html). – ruzenhack Jul 12 '17 at 19:29
is this still valid syntax in 2018?? – oldboy Jul 17 '18 at 01:51
I think so... Don't forget, this only works for procedures, not functions. – Agamemnus Oct 21 '18 at 22:52
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` – Raymond Nijland Dec 13 '18 at 14:27
What if I also need to store the result into a variable? SELECT x INTO @x FROM @y.... Is it possible with with CONCAT? – Tal Kohavy Apr 01 '21 at 19:32

1 Answers1