Exception handling of MySQL stored procedure empty result set error Error 1329 No data
In the stored procedure of MySQL, the following error occurs when an empty result set is queried
Error 1329 No data-zero rows fetched, selected, or processed
Solution:
In the stored procedure, add exception handling
Note the exception code in the orange part of the code
Delimiter $$
CREATE PROCEDURE PROC_ADDSubscribers_diff ()
BEGIN
DECLARE done INT DEFAULT FALSE
DECLARE Var_IMSI_NODE2 varchar (16)
DECLARE Var_MSISDN_NODE2 varchar (19)
DECLARE Var_IMEI_NODE2 varchar (16)
DECLARE Var_Timestamp_NODE2 bigint (32)
DECLARE Var_IMSI_NODE1 varchar (16)
DECLARE Var_MSISDN_NODE1 varchar (19)
DECLARE Var_IMEI_NODE1 varchar (16)
DECLARE Var_Timestamp_NODE1 bigint (32)
DECLARE Var_sqlcode INT DEFAULT 0
DECLARE cur1 CURSOR FOR select IMSI, MSISDN, IMEI, Timestamp from ADDSubscribers_node2
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE
OPEN cur1
Read_loop: LOOP
FETCH cur1 INTO Var_IMSI_NODE2, Var_MSISDN_NODE2, Var_IMEI_NODE2, Var_Timestamp_NODE2
IF done IS TRUE THEN
LEAVE read_loop
END IF
IF done IS FALSE THEN
IF (Var_IMSI_NODE2 is not null) THEN
BEGIN
DECLARE no_data CONDITION FOR 1329
DECLARE CONTINUE HANDLER FOR no_data
BEGIN
SET Var_sqlcode=2000
END
Select Timestamp, MSISDN, IMEI INTO Var_Timestamp_NODE1, Var_MSISDN_NODE1, Var_IMEI_NODE1 from dmcdbMTNGH.ADDSubscribers where IMSI = Var_IMSI_NODE2
IF Var_sqlcode = 2000 THEN
Start transaction
INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2
Commit
ELSEIF Var_sqlcode = 0 THEN
IF Var_Timestamp_NODE1 > = Var_Timestamp_NODE2 THEN
Select concat ('The data on node01 is newest') As Info
ELSE
IF (Var_MSISDN_NODE1 Var_MSISDN_NODE2) | | (Var_IMEI_NODE1 Var_IMEI_NODE2) THEN
Start transaction
INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2
Commit
END IF
END IF
END IF
END
END IF
END IF
END LOOP
CLOSE cur1
Select concat ('The job',' is', 'finishedstones') As Info
END$$
Delimiter
