CODEWITHSUNDEEP
mysql
jaip
jaip

Reputation: 31

MySQL function dont return select value correctly

I have created a mysql function to return value if exist otherwise insert and return the id. My function only work and return id of insert record, but if exist always return 0 (in this case, it should return the id). However, the SQL is working perfectly, when executed outside of function. 

DELIMITER $$
CREATE FUNCTION `GETSTOREID`(offer_id INT(5), affid INT(5), store VARCHAR(100)) RETURNS INT(5)
BEGIN
        DECLARE STOREID INT(5);

        IF EXISTS (SELECT store_id FROM `ccn_stores` WHERE `storeID` = offer_id AND `affiliate_id` = affid)
        THEN
        SET STOREID = (SELECT store_id FROM `ccn_stores` WHERE `storeID` = offer_id AND `affiliate_id` = affid);
    ELSE
        INSERT IGNORE INTO `ccn_stores` (`affiliate_id`, `storeID`, `store_name`) VALUES (affid, offer_id, store);
        SET STOREID = LAST_INSERT_ID();
        INSERT IGNORE INTO `ccn_store_configuration` (`store_id`) VALUES (STOREID);
    END IF;

        RETURN STOREID;
    END$$

DELIMITER ;

Upvotes: 0

Views: 139

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This condition:

   IF EXISTS (SELECT store_id FROM `ccn_stores` WHERE `storeID` = offer_id AND `affiliate_id` = affid)

does not make sense.

Why are you comparing StoreId -- which is NULL to anything. And why are you comparing something that is a store to an offer?

When using variables, I strongly encourage you to use some naming convention so they are not confused with columns. The stored function could start as:

CREATE FUNCTION GETSTOREID (
    in_offer_id INT(5),
    in_affid INT(5),
    in_store VARCHAR(100)
) RETURNS INT(5)
BEGIN
    DECLARE v_STOREID INT(5);
    . . .

Upvotes: 1

Related Questions