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

asked 28 Aug '14, 19:58

Scott%20Baldwin's gravatar image

Scott Baldwin
1567914
accept rate: 0%

edited 28 Aug '14, 20:00


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

permanent link

answered 29 Aug '14, 02:09

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213460
accept rate: 15%

edited 29 Aug '14, 02:10

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);
permanent link

answered 29 Aug '14, 04:23

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×13
×12

question asked: 28 Aug '14, 19:58

question was seen: 835 times

last updated: 29 Aug '14, 04:23