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
MySQL documention for FIND_IN_SET is here http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
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)
you have to create a dynamic statment:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `search_equipment`(IN equip VARCHAR(100), IN category VARCHAR(255))
BEGIN
SET @s =
CONCAT('SELECT *
FROM Equipment
WHERE e_description
LIKE \'%',equip,'%\'
AND e_type IN (',category,')');
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END$$
This helps for me to do IN condition Hope this will help you..
CREATE PROCEDURE `test`(IN Array_String VARCHAR(100))
BEGIN
SELECT * FROM Table_Name
WHERE FIND_IN_SET(field_name_to_search, Array_String);
END//;
Calling:
call test('3,2,1');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With