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

Answer 1

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.