CASE statement inside a function

[Former Member]

Hi all, I'm trying to make a convenience function that will expose an integer field as a human readable string, but for some reason I can't seem to get the syntax correct. I have tried many different approaches, but none seem to work. I am sure it is something fairly simple, but for the life of me I just can't seem to make a CASE statement work inside a FUNCTION. From my research, this should work

CREATE TEMPORARY FUNCTION RoleType(typeId INT)
RETURNS CHAR(30)
BEGIN
    DECLARE RoleType CHAR(30)
    CASE typeId
            WHEN 0 THEN SET RoleType = 'Own'
            WHEN 1 THEN SET RoleType = 'Child'
        WHEN 2 THEN SET RoleType = 'Root'
        WHEN 3 THEN SET RoleType = 'Shortcut'
    -- etc...
    END
    return RoleType
END


I know I could store these values in a look-up table, but honestly I'd rather just call the function than have to inner join a table every time I wanted to look up the role type, besides, my inability to make this work has frustrated me enough that I won't be happy until I've figured out what I'm doing wrong.
thanks

[thomas_duemesnil · Accepted solution]

It works this way.

CREATE TEMPORARY FUNCTION RoleType(typeId INT)
RETURNS CHAR(30)
BEGIN
    DECLARE @RoleType CHAR(30);
    CASE typeId
    WHEN 0 THEN 
        SET @RoleType = 'Own';
    WHEN 1 THEN 
        SET @RoleType = 'Child';
    WHEN 2 THEN 
        SET @RoleType = 'Root';
    WHEN 3 THEN 
        SET @RoleType = 'Shortcut';
    END;
    return @RoleType;
END


It seams that you can't declare a variable with the same name as the function. I did not realize this before. Thanks for letting me know 😉
Thomas

[VolkerBarth]

In addition to Thomas's answer, you might also use a CASE EXPRESSION instead of a CASE STATEMENT - that will omit all the "SET @RoleType = " statements:

DROP FUNCTION IF EXISTS RoleType;
CREATE TEMPORARY FUNCTION RoleType(typeId INT)
RETURNS CHAR(30)
BEGIN
    DECLARE @RoleType CHAR(30);
    SET @RoleType =
        CASE typeId
            WHEN 0 THEN 'Own'
            WHEN 1 THEN 'Child'
            WHEN 2 THEN 'Root'
            WHEN 3 THEN 'Shortcut'
                   ELSE '(unspecified)'
        END CASE;
    RETURN @RoleType;
END;

SELECT RoleType(1);
SELECT RoleType(null);