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.
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.
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
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.
*/
SELECTandSET