How to return a value in a Mysql database

Question

I have the following protocol:

DELIMITER $$
CREATE PROCEDURE sp_CalculaVolume (
    IN vReservatorioCodigo bigint,
    OUT Retorno decimal
)
BEGIN
    Declare Formato int;
    Declare TipoReservatorio bigint;
    Declare retorno decimal;
    Declare vAltura decimal;
    DECLARE vDiametro decimal;
    Declare vRaio decimal;
    Declare vAresta1 decimal;
    Declare vAresta2 decimal;

    SELECT      @Formato := IFNULL(t.Formato, 0),
                @vAltura := d.Altura,
                @vAresta1 := d.Aresta1,
                @vAresta2 := d.Aresta2,
                @vDiametro := d.Diametro
    From        TipoReservatorio t 
    INNER JOIN  Reservatorio r ON (t.Codigo = r.TipoReservatorioCodigo)
    INNER JOIN  DimensaoReservatorio d ON (t.DimensaoReservatorioCodigo = d.Codigo)
    WHERE       r.Codigo = vReservatorioCodigo;

    IF Formato = 1 THEN
        SET vRaio = vDiametro / 2;
        SET retorno = 3.14 * (vRaio * vRaio) * vAltura;
    ELSE
        SET retorno = vAresta1 * vAresta2 * Altura;
    END 
END$$
Delimiter ;

As can be seen, the return parameter is set to OUT and I would like to have this parameter returned when calling this parameter. But that is not the case. When I run:

CALL sp_RetornaValor(1, @val); 
SELECT @val

I have the following return:

As can be seen, it is not returning the OUT parameter, but the SELECT result I have inside the Procedure.

Answer 1

Hello, I believe this confusion is happening because you declared the exit paremeter "Return" with the capital "R",

OUT Retorno decimal

And then you are setting the value to a "return" variable with the "r" in lower case,

SET retorno = 3.14 * (vRaio * vRaio) * vAltura;
SET retorno = vAresta1 * vAresta2 * Altura;

I believe that if you change to "Return" will solve the problem.

SET Retorno = 3.14 * (vRaio * vRaio) * vAltura;
SET Retorno = vAresta1 * vAresta2 * Altura;

I hope I’ve helped ;)