CODEWITHSUNDEEP
mysqlstored-procedures
grhn
grhn

Reputation: 297

How to deal with missing rows in my stored procedure loop?

I have this procedure which loops through article values, fetch tag1 and insert it into article_tag table:

DELIMITER $$
CREATE PROCEDURE dt1()
BEGIN
    DECLARE maxid INT;
    DECLARE x INT;
    DECLARE t VARCHAR(30);
    DECLARE ntag1 int;
    SET maxid = (SELECT MAX(id) FROM `article`);
    SET x = (SELECT MIN(id) FROM  `article`) ;
    WHILE x<= maxid DO 
        SET t = (SELECT tag1 from `article` WHERE id=x);   
        SET ntag1 = (SELECT count(*) from `article_tag` WHERE tag=t);

        IF ntag1 = 0 
        THEN 
            INSERT INTO `article_tag` (tag, slug, frequency) VALUES (t, t, 1);
        ELSE 
            UPDATE  `article_tag` SET frequency = frequency + 1 WHERE tag=t;
        END IF;
        SET  x = x + 1; 
    END WHILE;
END$$

This works fine when there are rows with id in the while loop, but when when there are some missing ids in between (like here) I get 

Query Error: Error: ER_BAD_NULL_ERROR: Column 'tag' cannot be null

I'm wondering what is the idiomatic way to deal with such missing rows?

Upvotes: 0

Views: 378

Answers (1)

fifonik
fifonik

Reputation: 1606

You can use loop with cursor, so you will only loop through existing records and do not need to check for NULL. Something like this:

DELIMITER \\
CREATE PROCEDURE dt1()
BEGIN
    DECLARE t VARCHAR(30);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT tag FROM `article` ORDER BY id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;

    loop1: LOOP
        FETCH cur1 INTO t;
        IF done THEN
            LEAVE loop1;
        END IF;

        SELECT COUNT(*) INTO @cnt from `article_tag` WHERE tag = t;

        IF @cnt = 0  THEN 
            INSERT INTO `article_tag` (tag, slug, frequency) VALUES (t, t, 1);
        ELSE 
            UPDATE `article_tag` SET frequency = frequency + 1 WHERE tag = t;
        END IF;
    END LOOP;

    CLOSE cur1;
END
\\
DELIMITER ;

See manual for details: https://dev.mysql.com/doc/refman/5.7/en/cursors.html

Sure, if possible I'd use INSERT ON DUPLICATE UPDATE as already mentioned.

Upvotes: 2

Related Questions