[RESOLVED] Problem Returning 0 WHEN NULL

[mrbaseball34]

Got the function below.

I need to return 0 if it does not find a product:

delimiter //
DROP FUNCTION IF EXISTS lookup_product;
CREATE FUNCTION lookup_product (partno VARCHAR(255))
  RETURNS int
  DETERMINISTIC
  READS SQL DATA
  BEGIN
    RETURN (SELECT 
            CASE p.id_product
            WHEN NULL THEN 0
            ELSE p.id_product
            END AS id 
            FROM ps_product p
            INNER JOIN ps_feature_product pfp ON pfp.id_product = p.id_product
            INNER JOIN ps_feature_value_lang pfvl ON pfvl.id_feature_value = pfp.id_feature_value 
            WHERE pfvl.id_lang = 1
            AND pfvl.value = concat('"', partno, '"')
            LIMIT 1);
  END
//
delimiter ;          

Currently is is not returning 0 when it does not find the product, it is returning NULL.

Edit: Actually it is returning nothing not even blank when the table is empty, like it is currently.

[Crowly]

CASE WHEN p.id_product IS NULL THEN 0

[mrbaseball34]

Was actually able to get it running using this code:

delimiter //
DROP FUNCTION IF EXISTS lookup_product;
CREATE FUNCTION lookup_product (partno VARCHAR(255))
  RETURNS int
  DETERMINISTIC
  READS SQL DATA
BEGIN
    RETURN (SELECT CASE WHEN p.id_product IS NULL THEN 0 ELSE p.id_product END as id
            FROM ps_product p
            INNER JOIN ps_feature_product pfp ON pfp.id_product = p.id_product
            INNER JOIN ps_feature_value_lang pfvl ON pfvl.id_feature_value = pfp.id_feature_value 
            WHERE pfvl.id_lang = 1
            AND pfvl.value = partno
            LIMIT 1);
END
//
delimiter ;