Pass array to MySQL stored routine

Question

I need to pass an array of strings as parameter to a MySQL stored routine. The array could be long and its number of elements is not fixed. I then want to put the string values into an in-memory table with one column, so I can work with the data. I don't know if this can be done in MySQL. Maybe dirty workarounds are needed.

For example, I have the string values:

Banana, Apple, Orange

Now I want to get data on these fruits from my MySQL Fruits table. Pseudo code:

create function GetFruits(Array fruitArray) 
   declare @temp table as
      fruitName varchar(100)
   end

   @temp = convert fruitArray to table
   select * from Fruits where Name in (select fruitName from @temp)
end

Microsoft SQL Server allows you to use the TEXT datatype and submit the array as an XML string, swiftly creating the in-memory table. However, I don't think that technique is possible in MySQL.

Any help on how to do this would be appreciated!

Answer 1

You can pass a string with your list and use a prepared statements to run a query, e.g. -

DELIMITER $$

CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255))
BEGIN

  SET @sql = CONCAT('SELECT * FROM Fruits WHERE Name IN (', fruitArray, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END
$$

DELIMITER ;

How to use:

SET @fruitArray = '\'apple\',\'banana\'';
CALL GetFruits(@fruitArray);