Comma separated variable in MySQL

Question

I want to create a stored procedure in MySQL and one of the input parameters will need to be a comma separated list of integers. How do I loop through each integer and run an update statement on it?

I've googled it but can't seem to find anything that will work for MySQL.

Answer 1

Hmm... well, there is a discussion here in the MySQL reference down at the bottom about taking a string of comma-separated integers and doing a replacement on it to get it into something that you can use "IN" on it.

I'm assuming your input parameter is a VARCHAR here.

Answer 2

I got it...

DECLARE i INT DEFAULT 0;
DECLARE curId VARCHAR(100);
WHILE SPLIT_STR(in_IdCommaDelimited, ',', i) != '' DO

    SET curId = SPLIT_STR(in_IdCommaDelimited, ',', i);

    UPDATE MyTable SET OrderNumber = i WHERE Id = curId;

    SET i = i + 1;  

END WHILE;