How to Pass a List of String in MySQL Stored Procedure?

Question

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?

Community · Accepted Answer · 2017-05-23 10:27:17Z

11

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)

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Apr 19, 2013 at 11:43

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Osseweyer Over a year ago

Not only FIND_IN_SET is my friend, today you are my friend too.

drunken_monkey · Accepted Answer · 2013-04-19 11:43:03Z

3

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$$

answered Apr 19, 2013 at 11:43

drunken_monkey

1,8781 gold badge13 silver badges14 bronze badges

2 Comments

drunken_monkey Over a year ago

Simon at mso.net's SOlution is easier than mine, although probably not as fast. If your table is not huge (read millions of rows), the FIND_IN_SET solution is better.

hyamanieu Over a year ago

This is the best answer I received. Using FIND_IN_SET on my query lasted 48 seconds. Your (ugly) solution lasts 1.6 second. Not sure why we can't pass lists to procedures. This make the code unclear to have to use CONCAT

Raj · Accepted Answer · 2013-10-02 05:29:24Z

1

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');

answered Oct 2, 2013 at 5:29

Raj

7178 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to Pass a List of String in MySQL Stored Procedure?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related