NOT FOUND Error handling for Update Procedure in My SQL

Question

I am trying to write an sql stored procedure that will output an error message "ERROR! Campaign title does not exist" when there is no record corresponding to the input value (c_title). The procedure code is executing; however will not show the error message when I call the procedure for a value that does not exist.

Delimiter //
DROP PROCEDURE IF EXISTS sp_finish_campaign //

CREATE PROCEDURE sp_finish_campaign (IN c_title varchar(30))
BEGIN
    DECLARE not_found_indicator INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_indicator = 1;

    UPDATE campaign
    SET
    campaign.CAMPAIGNFINISHDATE = CURRENT_DATE(),
    campaign.ACTUALCOST = cost_of_campaign(campaign.CAMPAIGN_NO)
    WHERE campaign.TITLE = c_title
    AND campaign.ACTUALCOST = null
    AND campaign.CAMPAIGNFINISHDATE = null;

    IF not_found_indicator = 1
        THEN 
        SELECT 'ERROR! Campaign title does not exist' as 'msg';
    ELSE
        SELECT * FROM campaign
        WHERE campaign.TITLE = c_title;
    END IF;
END//
Delimiter ;

score 0 · Answer 1 · answered Oct 29 '19 at 09:26

An update not found does not fire the handler. The normal way of dealing with your requirement is to do an existence check, for example

drop procedure if exists p;
delimiter $$

CREATE PROCEDURE p (IN c_id int)
BEGIN
    DECLARE not_found_indicator INT DEFAULT 0;

     if not exists (select 1 from users where id = c_id) then 
         set not_found_indicator = 1;
     end if;

    IF not_found_indicator = 1
        THEN 
        SELECT 'ERROR! Campaign title does not exist' as 'msg';
    ELSE
            UPDATE users seT status = 1 where id = c_id;
        SELECT * FROM users WHERE id = c_id;
    END IF;
END $$
Delimiter ;

call p(1999);

NOT FOUND Error handling for Update Procedure in My SQL

1 Answers1