Hi,
I have a simple procedure:
DROP PROCEDURE IF EXISTS SP_SEARCH_DRUGDETAIL;
CREATE PROCEDURE SP_SEARCH_DRUGDETAIL(ps_drugid VARCHAR(50),
OUT ps_drugseq INT UNSIGNED,
OUT ps_publishdate DATETIME
)
BEGIN
DECLARE ER_SP_FETCH_NO_DATA CONDITION FOR 1329;
DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA
BEGIN
SET ps_drugseq = 0;
SET ps_publishdate = NULL;
END;
SELECT DRUG_SEQ, PUBLISHDATE
INTO ps_drugseq, ps_publishdate
FROM drug_master
WHERE DRUGID = ps_drugid AND ISBLOCKED = FALSE AND IS_PUBLISHED = TRUE;
END;
which plainly returns the drug details. If details are not found, exception gets caught by handler. However, what is happening is this:
Control goes to the handler. The parameters get set to 0 and NULL respectively BUT the exception also gets thrown back to the calling procedure and an exception occurs.
It doesnt make sense to have the handler in this procedure and also around the call:
<Piece of code of calling proc:>
IF ps_drugid1 IS NOT NULL AND length(trim(ps_drugid1)) > 0 THEN
begin
DECLARE ER_SP_FETCH_NO_DATA CONDITION FOR 1329;
DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA
begin
select 'this is not making sense';
end;
CALL SP_SEARCH_DRUGDETAIL(ps_drugid1,V_DRUGSEQ, @V_PUBLISHDATE);
end;
END IF;
Despite the handler in body of SP_SEARCH_DRUGDETAIL, I get the parameter V_DRUGSEQ set to 0 AND I also have the control going to exception handler in calling proc and 'this is not making sense' gets displayed.
Am I missing something in the code?