MYSQL - Stored Procedure Utilising Comma Separated String As Variable Input

Question

Im hoping someone will be able to help. I have created my first stored procdure (nothing fancy) however im running into an issue.

I want to give it a string input such as 1,2,3,4,5 then it does a simple SELECT * FROM [TABLE] WHERE EAN IN (VAR);

So the stored proc looks like this:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `moments`.`new_procedure`(IN var1 VARCHAR(255))
BEGIN

SELECT * FROM moments.PRODUCT WHERE EAN IN (var1);

END

Im am trying to execute it like such:

Works

call moments.new_procedure('5045318357397')

Does Not Work

call moments.new_procedure('5045318357397,5045318357427');

This executes but doesnt not bring back any results. Is it classing the second statement as a string so its doing this:

select * from moments.PRODUCT WHERE EAN IN ('5045318357397,5045318357427')

and not this:

select * from moments.PRODUCT WHERE EAN IN ('5045318357397','5045318357427')

How do i have to format the input in the execute query to get it to take a comma separated string as an input?

anothershrubery · Accepted Answer · 2012-09-17 16:12:17Z

22

You could use:

SELECT * FROM moments.PRODUCT 
WHERE FIND_IN_SET(EAN, var1)

This should work assuming it is actually comma delimited. Any other delimiting will not work in this case.

answered Sep 17, 2012 at 16:12

anothershrubery

21.1k16 gold badges56 silver badges102 bronze badges

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

3 Comments

mmkd Over a year ago

This works perfectly inside the stored proc, thanks you. This is also how i will be calling it from Cast Iron application, which is an added bonus.

Populus Over a year ago

this works, but it is not very efficient as it needs to fetch all rows from the table then compare each row to your set, which in turn also has to loop through the comma separated list to check if it matches... If your # of columns is low, then it's fine otherwise you should find a batter way

Carlos P Over a year ago

@Populus comment needs to be uprooted more - FIND_IN_SET does not use any table indices, so is potentially a huge performance problem if used in this way

a1ex07 · Accepted Answer · 2012-09-17 16:06:29Z

2

Assuming the string you passed is validated somehow and doesn't contain malicious sql, you can use prepared statements :

PREPARE stmt1 FROM CONCAT('select * from moments.PRODUCT WHERE EAN IN (',var1,')');
EXECUTE stmt1;

answered Sep 17, 2012 at 16:06

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Collectives™ on Stack Overflow

MYSQL - Stored Procedure Utilising Comma Separated String As Variable Input

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related