How to pass a variable to a IN clause?
35,924
Solution 1
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);
Solution 2
create a user-defined function that will convert the comma separated value into table, and by join this two can get the desired result.
Solution 3
passing a string using a variable was a problem assume this solution
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spTestListValues`(_list varchar(200))
BEGIN
SET @LIST=_list; -- assume this a paramter from the stored procedure
SELECT NULL AS Id, '' AS Description --insert null value to be used for list box population
UNION
(SELECT id, Description
FROM test_table
WHERE FIND_IN_SET(id,@LIST) ORDER BY Description ASC) ;
END
Calling the procedure from other query window
call `spTestListValues`('4,5,3'); --no paramter currently for test
output
ID Description
NUll
1 TEST1
4 TEST2
5 TEST3
![Thanu](https://i.stack.imgur.com/QmxVR.jpg?s=256&g=1)
Comments
-
Thanu about 3 years
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 belowSELECT product_id, product_price FROM product WHERE product_type IN (input_variables);
But its not working that way. Any idea how to do this?
-
No Idea For Name about 7 yearsthis will cause the query to run on all the table without index. Not robust even thought this is a valid answer