Question 137008 in Mysql Question posted in
The official MySQL documentation can be found here.

how to use case with condition inside a function in mysql?

DELIMITER $$

USE `db_sp`$$

DROP FUNCTION IF EXISTS `bil`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `bil`(num INT) RETURNS VARCHAR(7) CHARSET latin1
BEGIN
    DECLARE result VARCHAR(7);
    CASE num
    WHEN num > 0 THEN
    SET result = "Positive";
    WHEN num < 0 THEN
    SET result = "Negative";
    ELSE SET result = "Neutral";
    END CASE;
    RETURN result;
    END$$

DELIMITER ;

when I run SELECT bil(4); the result was Neutral. I think it should’ve been Positive. Did I miss something?

Tags:

2

Answers


  1. 0

    You can also do it by moving the set outside the case statement :

    CREATE FUNCTION `bil`(num INT) 
  RETURNS VARCHAR(20) CHARSET latin1
BEGIN
    DECLARE result VARCHAR(20);
    SET result = CASE WHEN num > 0 
                        THEN "Positive" 
                      WHEN num < 0 
                        THEN "Negative" 
                      ELSE "Neutral" END;
    RETURN result;
end;
    Login or Signup to reply.
  2. 0

    the CASE statement has two alternative syntaxes, and you’re mixing both:

    CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

    It is not wrong per-se, it just renders incorrect results for your use case because you’re comparing num with num > 0 (which equals 1 when num is 4) and with num < 0 (which equals 0). None match, so you get to the ELSE clause.

    Get rid of the extraneous num on top:

    CASE /*num*/
    WHEN num > 0 THEN
        SET result = "Positive";
    WHEN num < 0 THEN
        SET result = "Negative";
    ELSE SET result = "Neutral";
END CASE;

    Additionally, you need VARCHAR(8) rather than VARCHAR(7).

    Demo

    On a side note, latin1 is a legacy encoding you should try to avoid in 2023. Switch to utf8mb4 whenever possible. And MySQL is one of the few database engines that accept double quotes for strings, which the SQL standard reserves for identifiers—I’d suggest you get used to single quotes to avoid surprises when working with other systems.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search