Geek asks and answers

MYSQL - a stored procedure using a comma separated string as an input variable

I hope someone can help. I created my first stored procdure (nothing out of the ordinary), but I ran into a problem.

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

So, the stored procedure is as follows:

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

I try to execute it like this:

Work

call moments.new_procedure('5045318357397')

Does not work

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

Running but not returning any results. Does it split the second statement as a string, so does this:

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

not this:

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

How do I format the input in an execute request to force it to take a comma delimited string as input?

+5
source share
2 answers

You can use:

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

, . .

+13

, - sql, :

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

More articles:


All Articles