How to return a value in a MySQL procedure

1

I have the following procedure:

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 procedure. But this is not what happens. When I run:

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

I have the following return:

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

    
asked by anonymous 16.08.2018 / 15:35

1 answer

1

Hello, I believe this confusion is happening because you have declared the output parameter "Return" with the "R" in upper case,

OUT Retorno decimal

And then 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 have helped;)

    
16.08.2018 / 16:31