MySQL: SQL Error (1329): No data - zero rows fetched, selected, or processed

Every time I called the below Stored Procedure, I received the following SQL Error:

SQL Error (1329): No data - zero rows fetched, selected, or processed

It turned out I was missing a continue handler when the cursor finished looping. Adding this to the top fixed it:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

delimiter $$

DROP PROCEDURE IF EXISTS db.ProcedureLeadTransform$$

CREATE DEFINER = 'user'@'localhost' PROCEDURE db.ProcedureLeadTransform() READS SQL DATA COMMENT 'Convert comma delimited procedure ids in a field into a separate table' BEGIN

DECLARE l_id INT; DECLARE hasComma TINYINT; DECLARE commaPos SMALLINT; DECLARE thisPos SMALLINT; DECLARE numOfIds SMALLINT; DECLARE done INT DEFAULT 0; DECLARE l_procedureIds VARCHAR(1024); DECLARE l_db_added_dt DATETIME; DECLARE l_isActive TINYINT; DECLARE sequence TINYINT; DECLARE thisLength SMALLINT; DECLARE thisElementCount SMALLINT; DECLARE thisProcedureId MEDIUMINT; DECLARE ErrorCode1452 TINYINT DEFAULT FALSE;
-- Lead Procedures DECLARE cur_leads_procs CURSOR FOR SELECT leads.id, leads.procedureIds, leads.date, leads.isActive FROM db.leads LEFT JOIN db.leadprocedures p ON p.leadId = leads.id WHERE p.leadId IS NULL AND leads.procedureIds IS NOT NULL AND leads.procedureIds <> "" ORDER BY leads.id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN SET ErrorCode1452 = TRUE; END;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Lead procedures SET done = 0;
OPEN cur_leads_procs; leadsProcsLoop: LOOP FETCH cur_leads_procs INTO l_id, l_procedureIds, l_db_added_dt, l_isActive;
IF done = 1 THEN SET done = 0; LEAVE leadsProcsLoop; END IF;
-- Loop through list of ids SET sequence = 1; SET numOfIds = 0; SET commaPos = LOCATE(",",l_procedureIds); SET thisPos = 1;
IF (commaPos > 0) THEN SET thisLength = commaPos-1; ELSE SET thisLength = length(l_procedureIds); END IF;
SET thisElementCount = 1; SET numOfIds = (length(l_procedureIds)-length(replace(l_procedureIds,',',''))) + 1;
LOOPLIST: LOOP
SET thisProcedureId = SUBSTRING(l_procedureIds, thisPos, thisLength);
IF (thisProcedureId REGEXP "^[0-9]+$" AND thisProcedureId <> 0) THEN REPLACE INTO db.leadprocedures (leadId, procedureID, createdAt, deletedAt) VALUES (l_id, thisProcedureId, l_db_added_dt, IF(l_isActive = 0, now(), NULL));
IF ErrorCode1452 THEN #SHOW ENGINE INNODB STATUS; SELECT "Message: Cannot add or update a child row: a foreign key constraint fails"; SELECT l_id AS leadId, thisProcedureId AS procedureID, l_db_added_dt AS createdAt, IF(l_isActive = 0, now(), NULL) AS deletedAt; LEAVE leadsProcsLoop; END IF; END IF;
IF (thisElementCount = numOfIds) THEN LEAVE LOOPLIST; END IF;
SET sequence = sequence + 1; SET thisPos = commaPos+1; SET commaPos = LOCATE(",",l_procedureIds, thisPos); IF (commaPos > 0) THEN SET thisLength = commaPos-thisPos; ELSE SET thisLength = length(l_procedureIds); END IF; SET thisElementCount = thisElementCount + 1;
END LOOP LOOPLIST;
END LOOP leadsProcsLoop; CLOSE cur_leads_procs;

end;

Comments

New Comment