Return Zero in Function Mysql if query is NULL

Asked

Viewed 602 times

4

I have this function: 

BEGIN

RETURN( 
    SELECT valor_desconto AS desconto
    FROM ItemCardapio 
    WHERE id_produto = idProduto AND id_cardapio = idCardapio 
);
END

How do I return 0 if the consultation is NULL?

mysql

2 answers

5


As your problem lies in the return of your SELECT, you will have to declare a variable in the function to store the result of the SELECT and before returning the variable, check if it is NULL with the function IFNULL:

DELIMITER $$

DROP FUNCTION IF EXISTS getValorDesconto $$

CREATE FUNCTION getValorDesconto()
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE valor_desconto DECIMAL(10, 2);

    SELECT valor_desconto INTO valor_desconto
    FROM ItemCardapio;

    RETURN IFNULL(valor_desconto, 0);
END $$

DELIMITER;

Another way to do it is to put the SELECT within the IFNULL:

BEGIN
    RETURN IFNULL(
        (
            SELECT valor_desconto AS desconto
            FROM ItemCardapio 
            WHERE id_produto = idProduto AND id_cardapio = idCardapio
        ),
        0
    );
END;

The function IFNULL receives two parameters if the first one is different from NULL, returns himself, but if the first is NULL the second will be returned.

See more about the function in documentation.

  • I tried. It doesn’t work.

  • From what I understand, in case there is no record in the query it returns NULL... But in case I want to return 0.

  • @Rafaelsilva, I changed the answer!

  • @Robertodecampos, is it necessary to create a variable for this? In SQL Server just do SELECT ISNULL(campo, 0) nome_campo FROM tabela;

  • 1

    @bruno101, In his case the problem is that the SELECT does not return any line, but would also do with the IFNULL outside the SELECT as our friend @Pedropaulo did in his reply, the difference would be only the function.

  • @Robertodecampos, I understood, in this case the variable serves to force a return even if there is nothing inserted

  • Exactly, but anyway I added an example with the SELECT within the IFNULL.

Show 2 more comments

4

Use the function COALESCE() that is standard ANSI (standardization of similar functions for all DBMS). Your query does not bring any lines, so we will do a sub-query that will return us null, with this, we will use the COALESCE to return 0:

BEGIN

RETURN( 
    SELECT COALESCE((SELECT valor_desconto AS desconto
                       FROM ItemCardapio 
                      WHERE id_produto = idProduto 
                        AND id_cardapio = idCardapio), 0)
);
END

The function COALESCE is using two parameters if the first one is different from NULL, returns the field value, otherwise return the value of the second parameter.

  • The function COALESCE takes one or more parameters and returns the first non-zero. If all are NULL You will be returned NULL.

  • I made for this scenario of it, only need 1 parameter.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.