PROCEDURE ERROR PERFORMS ONLY ONCE AFTER GOING MYSQL CONDITION

Question

I have a problem in my past, the scenario is that I have null and not null values that are primary key fields, I am making a Datawarehouse. As all these values need to be calculated, when running the Procedure, if any of the values that are of the primary key passes in condition so that it does not enter null the Procedure runs only on the first line, then it stops, even though it has other data to run.

USE `poli_datawarehouse`;
DROP PROCEDURE IF EXISTS `quantMessage`;

DELIMITER $$
USE `poli_datawarehouse`$$
CREATE PROCEDURE `quantMessage`()
BEGIN
DECLARE fimDoCursor INT DEFAULT 0;
DECLARE customerId INT;
DECLARE channelId INT;
DECLARE userId INT;
DECLARE contactId INT;
DECLARE messageType VARCHAR(255);
DECLARE messageDir VARCHAR(2);
DECLARE createdAt DATE;
DECLARE quantMsgType INT;
DECLARE quantInsertMessage INT;
DECLARE idCustomer INT;
DECLARE quantIdCustomer INT;
DECLARE idUser INT;
DECLARE quantIdUser INT;
DECLARE idChannelCustomer INT;
DECLARE quantIdChannelCustomer INT;
DECLARE idMessage INT;
DECLARE auxiliarIdMessage INT DEFAULT 0;
DECLARE c CURSOR FOR 
SELECT id, customer_id, channel_id, user_id, contact_id, message_type, message_dir, created_at 
FROM polichat.messages LIMIT 10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fimDoCursor = 1;
OPEN c;
WHILE fimDoCursor = 0
DO
    FETCH c INTO idMessage, customerId, channelId, userId, contactId, messageType, messageDir, 
    createdAt;
    IF fimDoCursor = 0 THEN
        SELECT id_customers INTO idCustomer FROM poli_datawarehouse.customers WHERE 
    id_poli_customers = customerId;

        SELECT id_users INTO idUser FROM poli_datawarehouse.users WHERE id_poli_users = 
    userId;

        SELECT id_channel_customer INTO idChannelCustomer FROM 
    poli_datawarehouse.channel_customers WHERE id_poli_channel_customer = channelId;
    
        IF ISNULL(idCustomer) OR idCustomer = '' THEN
        SELECT COUNT(id_customers) INTO quantIdCustomer FROM poli_datawarehouse.customers 
        WHERE name = 'Não há valor referente';
            IF quantIdCustomer = 0 THEN
                INSERT INTO poli_datawarehouse.customers(name, created_at) VALUES ('Não há 
        valor referente', CURDATE());
            END IF;
                SELECT id_customers INTO idCustomer FROM poli_datawarehouse.customers WHERE 
        name = 'Não há valor referente';
        END IF;
    
        IF ISNULL(idUser) OR idUser = '' THEN
            SELECT COUNT(id_users) INTO quantIdUser FROM poli_datawarehouse.users WHERE name = 
        'Não há valor referente';
            IF quantIdUser = 0 THEN
                INSERT INTO poli_datawarehouse.users (name, created_at) VALUES ('Não há valor 
         referente', CURDATE());
            END IF; 
                SELECT id_users INTO idUser FROM poli_datawarehouse.users WHERE name = 'Não há 
         valor referente';
         END IF;
        
        IF ISNULL(idChannelCustomer) OR idChannelCustomer = '' THEN
            SELECT COUNT(id_channel_customer) INTO quantIdChannelCustomer FROM 
         poli_datawarehouse.channel_customers WHERE name = 'Não há valor referente';
            IF quantIdChannelCustomer = 0 THEN
                INSERT INTO poli_datawarehouse.channel_customers (name, created_at) VALUES 
         ('Não há valor referente', CURDATE());
            END IF;         
                SELECT id_channel_customer INTO idChannelCustomer FROM 
         poli_datawarehouse.channel_customers WHERE name = 'Não há valor referente';
         END IF;
    
        SELECT COUNT(*) INTO quantMsgType FROM polichat.messages WHERE message_type NOT LIKE 
        'sys%' AND message_dir COLLATE utf8mb4_unicode_ci NOT LIKE 'l' 
        AND DATE(created_at) = createdAt AND user_id = idUser AND customer_id = idCustomer AND 
        message_dir LIKE messageDir AND message_type LIKE messageType 
        AND channel_id = idChannelCustomer AND contact_id = contactId;
        
        
        SELECT COUNT(*) INTO quantInsertMessage FROM poli_datawarehouse.quant_message WHERE 
        DATE(created_at) = createdAt AND id_user = idUser AND id_customer = idCustomer 
        AND message_dir LIKE messageDir AND message_type LIKE messageType AND 
        id_channel_customer = idChannelCustomer AND id_contact = contactId;
        IF quantInsertMessage = 0 THEN
            INSERT INTO poli_datawarehouse.quant_message(id_customer, id_user, 
        id_channel_customer, id_contact, message_type, message_dir, created_at, 
        quant_msg_type)
            VALUES(idCustomer, idUser, idChannelCustomer, contactId, messageType, messageDir, 
        createdAt, quantMsgType);
        END IF;
        END IF;
        END WHILE;
        CLOSE c;
        END$$
        DELIMITER ;

All fields in my select form a single key. I was able to identify that the 'fimDoCursor' after receiving a null value of some of my conditions it arrow as 1, and for the Procedure, but I could not yet solve the problem. I tried to pick up the bug so it would continue to process anyway, and it didn’t work.

Answer 1

Apparently, when using INTO in some query it returns 1, with this it was giving error inserting only one because it assigned my variable endDoCursor the value of one or if I removed DECLARE CONTINUE HANDLER FOR NOT FOUND SET fimDoCursor = 1; it recorded the data they were supposed to record, but returned the error SQL Error [1329] [02000]: No data - zero rows fetched, selected, or processed I got it with my colleague Ricardo Portugal’s suggestion to use the LOOP and the suggestion found here, then my past became like this:

loop_label: LOOP
    FETCH c INTO ...;
    if fimDoCursor = 1 then
        LEAVE loop_label;
    END IF; 
    
    ~minhas instruções~

    SET fimDoCursor = 0; -- reseto o minha variável para ela mudar para 1 somente quando acabar todos os dados que precisam ser inseridos e cair no if especificado acima
    END LOOP loop_label;