How to pass a variable to a IN clause?

mysql variables stored-procedures in-clause
Thanu picture Thanu · Oct 29, 2012 · Viewed 34.4k times · Source

Lets say I have a SP that has a SELECT statements as follows,

SELECT product_id, product_price FROM product 
WHERE product_type IN ('AA','BB','CC');

But data goes to that IN clause must be through a single variable that contains the string of values. Something link below

SELECT product_id, product_price FROM product 
WHERE product_type IN (input_variables);

But its not working that way. Any idea how to do this?

Answer

Devart picture Devart · Oct 29, 2012

Pass parameter value like this - 'AA,BB,CC'. Then, it is enough to use FIND_IN_SET function -

SELECT product_id, product_price
FROM product
WHERE FIND_IN_SET(product_type, param);

Related questions

Set the variable result, from query

When I create the saved procedure, i can create some variable yes? for example: CREATE PROCEDURE `some_proc` () BEGIN DECLARE some_var INT; SET some_var = 3; .... QUESTION: but how to set variable result from the query, that is how to …

Read More
MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More