Procedure to loop through comma separated string is not working

Question

I have corrected the code with the help of answer given in stack overflow. I want to loop through the comma separated string of Ids but not able to do so. Below given procedure only updates first record and not updating other records. What corrections are required so that I can loop through comma separated string. This is the code for my SP

BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
   SET strIDs = '';
  END IF;

do_this:
LOOP
SET strLen = LENGTH(strIDs);

UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
SET strIDs = MID(strIDs, SubStrLen, strLen);

IF strIDs = NULL THEN
  LEAVE do_this;
END IF;
END LOOP do_this;
END

The code is as provided in the answers for this post.

I tried with find_in_set() function but it works only if I pass ids start from beginning and not working if I pass Ids randomly. This is my script for the table

CREATE TABLE `testtable` (
    `Id` int(11) DEFAULT NULL,
    `Status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of testtable
-- ----------------------------
INSERT INTO `testtable` VALUES ('1', 'O');
INSERT INTO `testtable` VALUES ('2', 'O');
INSERT INTO `testtable` VALUES ('3', 'O');
INSERT INTO `testtable` VALUES ('4', 'O');
INSERT INTO `testtable` VALUES ('5', 'O');

This is the stored procedure

BEGIN
  UPDATE TestTable SET status = 'C' WHERE Id = FIND_IN_SET(Id, strIDs);
END

strIds is varchar type.

Now try @strIDs='2'

Answer 1

Try this one (syntax errors are fixed and without CAST function) -

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = NULL THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

You can debug your procedure with Debugger for MySQL.

EDIT2:

I'd do it without procedure. Try to use FIND_IN_SET function, e.g. -

SET @strIDs = '1,2,3'; -- your id values
UPDATE TestTable SET status = 'C' WHERE FIND_IN_SET(id, @strIDs);

Or create a temp. table, fill it with id values and join these two tables in UPDATE statement.

Answer 2

I didn't find any suitable method, so I ended up making my own. It's pretty simple.

PROCEDURE db.loop_through_array()
BEGIN

  DECLARE strIDs varchar(150) DEFAULT 'one,two,three';
  DECLARE element varchar(150);

  WHILE strIDs != '' DO

    SET element = SUBSTRING_INDEX(strIDs, ',', 1);      

    UPDATE TestTable SET status = 'C' WHERE Id = element;

    IF LOCATE(',', strIDs) > 0 THEN
      SET strIDs = SUBSTRING(strIDs, LOCATE(',', strIDs) + 1);
    ELSE
      SET strIDs = '';
    END IF;

  END WHILE;

END

Answer 3

Maybe this post is a bit too old but I have try the code presented by Devart and it is not working for me.

With few modification, this version works for me :

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = CHAR_LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = '' THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

Explanations:

1) Why "+2" IN SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;

When you execute the MID function on the next line, string index begin with 1. If you have the following string '4500,2', with the Devart version, MID Looks like MID('4500,2',4,6) which is return ',2'.

So if you add 1 at the substring length, you are on the delimiter. It's not enough. So you add the length of the delimiter. Now it's good.

2) Why IF strIDs = '' THEN in the loop condition?

Because when you do MID you return a string even if this string are empty.

Devart procedure are patched ! Thank a lot for you're anwser :)