CODEWITHSUNDEEP
mysqlvariableswhere-clausein-clause
Sandeepan Nath
Sandeepan Nath

Reputation: 10284

using mysql variable to hold comma separated value to be used for where in clause

I have to run a query like this (query 1) - 

select something from sometable where someId in (1,2,3)

I would like to keep a variable for the IDs part, like this (query 2) - 

set @myIds = "1,2,3";
select something from sometable where someId in (@myIds);

But this does not give the expected result (gives an empty result set), and no query error as well.

I checked that if I wrap the comma separated IDs inside quotes, the query results an empty result set (query 3) - 

select something from sometable where someId in ("1,2,3");

I guess when I am using variable @myIds like I showed above (query 2), it is evaluating to the above query (query 3).

Upvotes: 10

Views: 3548

Answers (2)

Vatev
Vatev

Reputation: 7590

The proper (and also more complicated) way to do that would be a temp table:

DROP TEMPORARY TABLE IF EXISTS `some_tmp_table`
CREATE TEMPORARY TABLE `some_tmp_table` (
    `id` INT(10) UNSIGNED NOT NULL
) ENGINE=MEMORY #memory engine is optional...

Insert your ID's the temp table 

INSERT INTO some_tmp_table VALUES (1),(2),(3)...

and then use a JOIN instead of IN().

SELECT something 
FROM sometable s
JOIN some_tmp_table ts ON ts.id = s.someId

The other way is to use dynamic SQL as the other answer suggests. It might be simpler for you to generate the dynamic SQL in your app, but you can do it in MySQL too.

Upvotes: 2

John Woo
John Woo

Reputation: 263723

You need to have a dynamic sql on this,

SET @myIds = '1,2,3';
SET @sql = CONCAT('select something from sometable where someId in (',@myIds,')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 7

Related Questions