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.