Can multiple handlers be declared?

[J D]

I have 2 problems, which may be related to eachother.

The first problem I encounter is when I have 2 exit handlers. It seems as though the first one works, but the second one gets ignored. (By ignored, I mean that the function would execute the same if you never wrote the handler into the code) This also happens when I switch the order of the handlers. I also tried using continue instead of exit and had the same results.

The second problem I am having is with the first select statement. If I try to do a 'select' instead of a 'select into' I get a error number 1415: 'Not allowed to return a result set from a function.' I would understand this error if there was a return statemnt inside the if block, but there is not. More code follows after the end of the 'if' block before a return statement is encountered.

Am I doing something wrong?

DELIMITER $$

DROP FUNCTION IF EXISTS func_test$$
CREATE FUNCTION func_test(
valueA INT(11),
valueB INT(11),
) RETURNS int(11)
BEGIN
DECLARE STATUS_SUCCESS INT DEFAULT 1;
DECLARE STATUS_UNKNOWN INT DEFAULT 2;
DECLARE STATUS_DUPLICATE INT DEFAULT 3;
DECLARE STATUS_INVALID INT DEFAULT 4;

DECLARE tempVal INT default 0;
DECLARE exit handler for 1329 return STATUS_INVALID;
DECLARE exit handler for 1062 return STATUS_DUPLICATE;


/* Make sure the values passed in are valid. If no rows
are returned, Throw 1329 (which will exit this function call). */
if (valueB != 0) then
select myD into tempVal
from myTable
where myB=valueB
and myA=valueA;
end if;

/* If 1329 was not thrown, do an insert. If this is a
duplicate entry, 1062 should be thrown here*/
insert into myTable (myB, myA, myD)
values (valueB, valueA, 'blah');

/* Determine if the insert was successful. */
select row_count() into tempVal;

/* Return the state of the insert operation. */
return if( tempVal = 1, STATUS_SUCCESS, STATUS_UNKNOWN );

END$$

DELIMITER ;



Edited 1 time(s). Last edit at 08/14/2005 05:24PM by J D.