I'm trying to pass an array as a String in MySQL Stored Procedure but it doesn't work fine.
Here's my SQL Codes:
CREATE DEFINER=`root`@`localhost` PROCEDURE `search_equipment`(IN equip VARCHAR(100), IN category VARCHAR(255))
BEGIN
SELECT *
FROM Equipment
WHERE e_description
LIKE CONCAT("%",equip,"%")
AND e_type IN (category)
END
And here's how i call the procedure:
String type = "'I.T. Equipment','Office Supply'";
CALL search_equipment('some equipment', type);
Any ideas?
解决方案
Your friend here is FIND_IN_SET I expect. I first came across that method in this question : also covered in this question MYSQL - Stored Procedure Utilising Comma Separated String As Variable Input
So your procedure will become
CREATE DEFINER=`root`@`localhost`
PROCEDURE `search_equipment`(
IN equip VARCHAR(100),
IN category VARCHAR(255)
)
BEGIN
SELECT *
FROM Equipment
WHERE e_description LIKE CONCAT("%",equip,"%")
AND FIND_IN_SET(e_type,category)
END
This relies on the category string being a comma-delimited list, and so your calling code becomes
String type = "I.T. Equipment,Office Supply";
CALL search_equipment('some equipment', type);
(p.s. fixed a typo, in your arguments you had typed categoy)