CODEWITHSUNDEEP
mysqlparametersnamesworkbench
zaggi
zaggi

Reputation: 940

MySQL: How to pass a column name as parameter in MySQL query

Shows all cities containing "a" :

SELECT * FROM user WHERE city LIKE '%a%';  -- works fine

What is the correct syntax :

SET @param = 'city';
SELECT * FROM user WHERE @param LIKE '%a%'; -- NOT yielding any rows

Tested in MySQL Workbench query

Upvotes: 0

Views: 2190

Answers (2)

marek
marek

Reputation: 1

you can use 

select id,date,@statement as any_name from table_name ;
SET @statement = (SELECT To_days( date_f ) - TO_DAYS( date )  FROM `table_name `);

Upvotes: 0

zaggi
zaggi

Reputation: 940

The correct syntax goes like this:

SET @param = 'city';
SET @statement = CONCAT('SELECT * FROM user WHERE ', @param, ' LIKE ', '"%a%"');
PREPARE myquery FROM @statement;
EXECUTE myquery;
DEALLOCATE PREPARE myquery

Things to keep in mind:

  1. put spaces in the CONCAT params in line#2 where necessary as CONCAT won't put them for you, e.g. you (and MySQL also) would prefer :

... user WHERE city LIKE '%a%';

rather than :

... userWHEREcityLIKE'%a%'

  1. add extra AND different quotes :

'%a%' should become '"%a%"'

  1. DEALLOCATE in the end - you declared a global var 'myquery' valid by default till the session reset - may cause collisions and errors

  2. Tip To see the result of CONCAT(), use

SELECT @statement

Upvotes: 2

Related Questions