stored procedure not selecting data

Question

I am trying to execute select query manually in database console then it gives me result.But when i am using same select query with in procedure then its not working.

ERROR :

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Code :

DELIMITER $$
DROP PROCEDURE IF EXISTS abc_19;
CREATE PROCEDURE abc_19()
BEGIN
    DECLARE lc_current_time DATETIME;
    DECLARE lc_calc_value INT;
    DECLARE auto_assign TINYINT;
    DECLARE total_sum INT;
    DECLARE check_count INT;
    BEGIN
        DECLARE select_cursor CURSOR FOR SELECT count(id) as count,A.auto_assign, B.sum, truncate(B.sum*100.00/100,0) as check_count FROM cli_group_number A INNER JOIN (Select auto_assign, count(id) sum from cli_group_number where cli_group_id = 5 Group by auto_assign) B on A.auto_assign = B.auto_assign WHERE cli_group_id = 5 and sip_from_uri ='' Group by sip_from_uri, A.auto_assign;
        SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

        OPEN select_cursor;
        LOOP
            FETCH select_cursor INTO lc_calc_value,auto_assign,total_sum,check_count;
            IF lc_calc_value <= check_count THEN
                insert into report(current_date,alert_id,alert_name,type,status,email,trunk_cli_id,triggered_value,threshold_value) values (lc_current_time,19,'CLI',4,1,'[email protected]',5,check_count,lc_calc_value);
                INSERT INTO mails (`userid`,`date`,`subject`,`body`,`from`,`to`,`status`,`parent_id`) VALUES (1,lc_current_time,'Alarm : CLI',concat('Hello Admin,
                                                Name : abc,
                                                Type : def,
                                                Threshold : 100.00
                                                Period : 60
                                                Group : test 
                                                Value : ',lc_calc_value),'[email protected]','[email protected]',1,0);
            END IF;
        END LOOP;
        CLOSE select_cursor;
    END;
END$$

DELIMITER ;

Anyone please answer it instead of just edit and edit question ? I am not sure what is problem here.But try to learn so next time i can understand what should need to consider — Ankit Doshi
– Ankit Doshi, Commented Jun 15, 2018 at 13:52

Michael - sqlbot · Accepted Answer · 2018-06-16 00:41:41Z

Cursors throw an exception when you try to read past the end.

You have an endless loop in your code.

The exception is saving you from that.

Catch the exception so that you can exit the loop gracefully.

BEGIN
    -- create a variable to track whether the cursor is out of rows
    DECLARE done TINYINT DEFAULT FALSE;
    DECLARE select_cursor CURSOR FOR SELECT count(id) as count,A.auto_assign, B.sum, truncate(B.sum*100.00/100,0) as check_count FROM cli_group_number A INNER JOIN (Select auto_assign, count(id) sum from cli_group_number where cli_group_id = 5 Group by auto_assign) B on A.auto_assign = B.auto_assign WHERE cli_group_id = 5 and sip_from_uri ='' Group by sip_from_uri, A.auto_assign;
    -- create an exception handler to catch the "no data" condition and flip the value of "done"
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

    OPEN select_cursor;
-- label the loop
my_made_up_loop_label:
    LOOP
        FETCH select_cursor INTO lc_calc_value,auto_assign,total_sum,check_count;
        -- test whether the exception handler has caught the exception and flipped the value, and leave the loop if so; this requires a label for the loop
        IF done IS TRUE THEN
          LEAVE my_made_up_loop_label;
        END IF;
        IF lc_calc_value ...

Collectives™ on Stack Overflow

stored procedure not selecting data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related