MySQL select query with where condition using string variables

Question

Myself trying to pass string variable to where condition in MySQL query as given in this stack overflow answer as given below.

select @start := ' and  Id=21';

select * from myTable where 1=1 @start;

So how can I use string variable with where condition in MySQL queries. The variables are set dynamically and the query runs within procedure.

EDIT: I also tried

SET @start = ' Id=21 ';

select * from myTable where (select @start);

But no use.

... Wrap the string value in quotes? – Nathan Tuggy Dec 22 '15 at 05:05 — Nathan Tuggy, Dec 22 '15 at 05:05
No way.. I also tried both `SELECT` and `SET` – mpsbhat Dec 22 '15 at 05:09 — mpsbhat, Dec 22 '15 at 05:09

score 1 · Accepted Answer · edited May 23 '17 at 12:26

No you cannot do that. The columns and the condition in the select clause needs to be fixed when you are preparing the select statement.

So you cannot make a dynamic where clause statement like the one you posted. In that example, the values in the column are dynamic not the column names.

The manual says:

A conditional object consists of one or more conditional fragments that will all be joined by a specified conjunction. By default, that conjunction is AND.

score 1 · Answer 2 · edited May 11 '16 at 14:04

I believe what you are attempting is to create a Dynamic Query using EXEC command.

You can create a varchar variable with the SQL statement and then execute it with EXEC, here an example taken from

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

If you want to do something like

DECLARE @city varchar(75)

SET @city = 'London'

SELECT * FROM customers WHERE City = @city

This is the Dynamic Query creation.

DECLARE @sqlCommand varchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = 'CustomerID, ContactName, City'

SET @city = '''London'''

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city

EXEC (@sqlCommand) --This does the magic 


/*
just a heads up, the user impersonating the execution needs credentials for EXEC command.
*/

It's just that you must use not EXEC, but PREPARE and EXECUTE, because this question concerns MySQL. — ZygD, Mar 07 '17 at 12:37

score 1 · Answer 3 · answered Jul 11 '19 at 18:12

Store part of your query

SET @start = ' and  Id=21';

Store your query concatenating its parts

SET @s = CONCAT('select * from myTable where 1=1 ', @start);

Prepare a statement for execution

PREPARE stmt FROM @s;

EXECUTE executes a prepared statement

EXECUTE stmt;

Release the prepared statement

DEALLOCATE PREPARE stmt;

All together:

SET @start = ' and  Id=21';
SET @s = CONCAT('select * from myTable where 1=1 ', @start);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

More Details on the MySQL manual: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

MySQL select query with where condition using string variables

3 Answers3