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?

Answer 1

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.

Answer 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;