40

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?

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Thanu
  • 2,481
  • 8
  • 34
  • 53
  • 3
    where is `input_variables` coming from? `php?` – John Woo Oct 29 '12 at 04:56
  • its actually a direct MYSQL execute from a cron job, so its one of the input parameters of the SP. user enters them depends on what data they want. – Thanu Oct 29 '12 at 05:06
  • 1
    so you mean it is a comma separated value in a single variable? – John Woo Oct 29 '12 at 05:09
  • It should be. But I can make it the way I want (If I find the correct way to do it :)) , but it'll be a user input. – Thanu Oct 29 '12 at 05:52
  • 2
    well you can't. single variable eventhough it is a comma separated value will be interpreted as single value. Example, you have variable `x = '1','2','3','4'`. when you pass it to `in` like this, `WHERE y IN (x)`, it will be interpreted as `WHERE y IN ('1,2,3,4')` and not `WHERE y IN ('1','2','3','4')` – John Woo Oct 29 '12 at 05:55

4 Answers4

58

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);
Devart
  • 119,203
  • 23
  • 166
  • 186
3

create a user-defined function that will convert the comma separated value into table, and by join this two can get the desired result.

for more

solaimuruganv
  • 27,177
  • 1
  • 18
  • 23
2

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
Kaii
  • 20,122
  • 3
  • 38
  • 60
Develop4Life
  • 7,581
  • 8
  • 58
  • 76
-2

Try using a prepared statement:
https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Tomás
  • 3,501
  • 3
  • 21
  • 38
ErikHH
  • 93
  • 3