Re: Classic SP problem

[Roland Bouman]

Yes, Id use SELECT INTO too.

In addition, you could also use a NOT FOUND handler; this is more explicit then IF..IS NULL. :

DELIMITER //

CREATE PROCEDURE add_food (
IN name VARCHAR(50)
,IN type VARCHAR(50)
)
BEGIN
DECLARE l_type_pk INTEGER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
CALL p_insert_foodtype(type,l_type_pk)
;
SELECT foodtype_pk
INTO pk
FROM foodtype
WHERE foodtype_name = type
;
INSERT
INTO food (food_name, food_type)
VALUES (name, l_type_pk)
;
END;
//

CREATE PROCEDURE p_insert_foodtype(
IN type VARCHAR(50)
, OUT l_type_pk int unsigned
)
BEGIN
INSERT
INTO foodtype(foodtype_name)
VALUES (type)
;
SET l_type_pk := last_insert_id()
;
END
//


This way, you have a nice separation between the exceptional not exists and the business as usual exists case. Also, you know for sure you will branch only in the case of a NOT FOUND situation. No accidental tampering with the expression in the IF can occur. Another advantage is that the handler solution will work even in case the select into does retrieve a record of wicht the retrieved column happens to be NULL.

my 10cents.