MySQL : using user-defined variables for a table name

Question

I would like to use a user-defined variable in the FROM part of a query like in the example below :

SET @year = 2013,
    @ID3_1 = 107001001,
    @TableSource = "S_EXPO_RISQUES_METEO";

SELECT @ID3_1,
       @year,
       TS.ID_TER,
       TS.VALUE 
FROM @TableSource TS;

How can I produce the equivalent result with an other statement ?

Possible duplicate of [How to select from MySQL where Table name is Variable](https://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable) — cdaiga, Apr 10 '18 at 10:17
If you have to do this, your database is probably not set up very well. — GrumpyCrouton, Apr 10 '18 at 13:07

score 0 · Accepted Answer · answered Apr 10 '18 at 10:13

0

Try this to get an equivalent without using variables.

SELECT 107001001 ID3_1,
       2013 year,
       TS.ID_TER,
       TS.VALUE 
FROM S_EXPO_RISQUES_METEO TS;

Or this with variables:

SET @year = 2013,
    @ID3_1 = 107001001;

SELECT @ID3_1,
       @year,
       TS.ID_TER,
       TS.VALUE 
FROM S_EXPO_RISQUES_METEO TS;

answered Apr 10 '18 at 10:13

cdaiga

4,861
3
22
42

Thank you. The idea is to get a _generic_ code. I need to use it with tens of other tables and other users of the database will also need to do so. This is why it is not efficient enough like this... – Aberange Apr 10 '18 at 10:44
Then you just have to build the code in the application layer before running it against your database. – cdaiga Apr 10 '18 at 10:49

score 0 · Answer 2 · answered Apr 10 '18 at 10:16

You Can’t Do That™. Variables aren’t allowed for the naming of columns or tables in queries. The workarounds? Use your programming language (php? Java?) to create your query string.

Or, use MySQL server side prepared statements, not to be confused with php or Java prepared statements. Read this. https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

MySQL : using user-defined variables for a table name

2 Answers2