how to return in a mysql function

Question

Hy, I have the following function on my mysql schema, this function should work like this:

  if (especie=mEspecie && variedad=mVariedad) then
         then return precio;   else
         return 0; 
  end if;

but somehow it's not working, anybody can help me here?.

this is my function, I've updated since @vipin answer:

  CREATE DEFINER=`root`@`localhost` FUNCTION `BUSCA_PRECIO_MATRIZ`(mEspecie int, mVariedad int) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare precio, especie,variedad integer;
declare cur1 cursor for
select ifnull(valor,0),idconf_especie,idconf_variedad from cc_matriz_precios_facturacion_recibidor where idconf_especie=mEspecie and idconf_variedad=mVariedad;

open cur1;
    loop_cur : loop 
        fetch cur1 into precio,especie,variedad;
            if(especie = mEspecie) then
                if  (variedad = mVariedad) then
                    return variedad;
                    LEAVE loop_cur;
                else
                    return variedad;
                    leave loop_cur;
                end if;
            end if;
    end loop;
    return 0;
close cur1;
return 0;
END

why you written again and again return 0 or 1 ? what you want to write in procedure ? — Vipin Jain
– Vipin Jain, Commented Mar 23, 2016 at 14:40
so why you write cursor for it ? you can use it by only select query. i am update my query — Vipin Jain
– Vipin Jain, Commented Mar 23, 2016 at 15:06
because I use the result on other query's so I just wanted to call the function — jfernandez_04
– jfernandez_04, Commented Mar 23, 2016 at 15:12
maybe it is because Im comparing a string from my cursor with a int value from my function arguments??? — jfernandez_04
– jfernandez_04, Commented Mar 23, 2016 at 15:17

Vipin Jain · Accepted Answer · 2016-03-23 15:20:47Z

2

in my query, if any data found in cc_matriz_precios_facturacion_recibidor table after check condition then its return true other wise its return false

    CREATE FUNCTION `BUSCA_PRECIO_MATRIZ`(mEspecie int, mVariedad int) RETURNS int(11)
        READS SQL DATA
        DETERMINISTIC
    BEGIN
    declare precio, especie,variedad, flag int;    

    select valor,idconf_especie,idconf_variedad, 1 into precio, especie,variedad, flag 
    from cc_matriz_precios_facturacion_recibidor
    where idconf_especie=mEspecie and idconf_variedad=mVariedad;
    IF(flag) THEN 
         return 1; 
    ELSE 
         return 0;
    END IF;
    END

or in short

CREATE FUNCTION `BUSCA_PRECIO_MATRIZ`(mEspecie int, mVariedad int) RETURNS int(11)
            READS SQL DATA
            DETERMINISTIC
        BEGIN
        declare precio, especie,variedad int;   
        DECLARE flag int default 0;

        select valor,idconf_especie,idconf_variedad, 1 into precio, especie,variedad, flag 
        from cc_matriz_precios_facturacion_recibidor
        where idconf_especie=mEspecie and idconf_variedad=mVariedad;
        return flag;
        END

edited Mar 23, 2016 at 15:20

answered Mar 23, 2016 at 14:32

Vipin Jain

3,77618 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jfernandez_04 Over a year ago

Thanks for the quick response but it keep throwing the same response 'OK' I updated my function

wchiquito · Accepted Answer · 2016-03-23 17:24:30Z

1

Another option which simplifies its function is:

DELIMITER //

DROP FUNCTION IF EXISTS `BUSCA_PRECIO_MATRIZ`//

CREATE FUNCTION `BUSCA_PRECIO_MATRIZ` (
    `mEspecie` INT,
    `mVariedad` INT
)
RETURNS BOOL
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN EXISTS (SELECT NULL
                   FROM `cc_matriz_precios_facturacion_recibidor`
                   WHERE `idconf_especie` = `mEspecie` AND
                         `idconf_variedad` = `mVariedad`);
END//

DELIMITER ;

edited Mar 23, 2016 at 17:24

answered Mar 23, 2016 at 15:53

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Collectives™ on Stack Overflow

how to return in a mysql function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related