Using a variable in a table name without dynmaic SQL

Question

How can I use a variable in a SQL query without using dynamic SQL and the concat method?

I’d love to be able to declare variables at the start of a script to then use them throughout the script (e.g. table names)

Here’s an example of what I’d like to do:

declare variables
Set @Table1 = 'WhatsOn_20141208'

-- drop and re-create table
drop table if exists @Table1;
CREATE TABLE @Table1 (
rac_account_no  varchar(100),
addressLine2    varchar(100)
);

-- load data into table
LOAD DATA LOCAL INFILE 'C:/Example.txt' 
INTO TABLE @Table1
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- update addressLine2 column
Update @Table1
set addressLine2 = REPLACE(addressLine2,"*UNKNOWN*","");

If the table name changes, I want to be able to change it in the variables once rather than doing a find and replace of all occurrences.

The only solution I’ve found so far is using dynamic SQL and concatenating the string like this example:

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

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

Is there an easier way?

Cheers, Lucas

you can have the table name in a config file and in the .sql file have the macro table name, have a python program which reads the config file and modifies the sql file and use the modified sql for your purpose. — radar, Dec 11 '14 at 02:17
A database structure where you have multiple tables with the same columns is often the sign of problems in the database. There is very rarely any reason to have multiple identical tables. You should probably revisit your database design. — Gordon Linoff, Dec 11 '14 at 02:20
Hey Gordon, I just picked a random bit of code to illustrate what I was trying to do. Don't read too much into that specific example. Sounds like it's not possible anyway. — Lucas, Dec 11 '14 at 02:30

O. Jones · Accepted Answer · 2014-12-11T13:15:30.453

You asked, How can I use a variable in a SQL query without using dynamic SQL and the concat method?

Sorry, you can't. Dynamic SQL is the way that's done in MySQL.

This applies to the names of schemas, tables, and columns -- that is, data dictionary names -- and not to values.

Most application developers who need variable names for data dictionary items construct the queries in their host language. That is, they use php or Java or something like that to turn strings like

 SELECT xxx FROM yyy WHERE zzz

into strings like

 SELECT id,name,value FROM transaction WHERE id=?

They then proceed to use bind variables for the data values.

MySQL prepared statements are simply a way of doing that kind of work inside the MySQL server rather than in the host language. But (in my opinion) prepared statements are hard to unit-test and troubleshoot, so it's more efficient software engineering to use your host language for that.

It's a little hazardous when the application's data source isn't trusted, so it's important to check every input for validity.

Fair enough. So if you had the variables being fed from a form or some external source and passed into MySQL, you would use Dynamic SQL? Would you write a separate prepare/execute/deallocate for each query you were doing or would you combine multiple queries into one prepare/execute/deallocate? — Lucas, Dec 11 '14 at 02:20

Using a variable in a table name without dynmaic SQL

1 Answers1