/**
 * This sample demonstrates how to return the results
 * of a CASE statement in a short way without wrapping
 * it into BEGIN/END, declaring and setting variables
 * and returning the result at the end.
 *
 * Author: Jan Beilicke <dev@jotbe-fx.de>
 * Date: 2011-07-06
 */
DROP FUNCTION IF EXISTS caseTestShort;
DELIMITER //

CREATE FUNCTION caseTestShort(str VARCHAR(10))
RETURNS VARCHAR(10) DETERMINISTIC
CASE str
    WHEN 'foo' THEN
        RETURN 'bar';
    WHEN 'bla' THEN
        RETURN 'fasel';
    ELSE
        RETURN 'Not found';
END CASE;
//

DELIMITER ;

SELECT
    caseTestShort('foo') AS foo,
    caseTestShort('bla') AS bla,
    caseTestShort('bar') AS bar;

/**
 * The long form in comparison
 */

DROP FUNCTION IF EXISTS caseTestLong;
DELIMITER //

CREATE FUNCTION caseTestLong(str VARCHAR(10))
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN

DECLARE res VARCHAR(10);

CASE str
    WHEN 'foo' THEN
        SET res = 'bar';
    WHEN 'bla' THEN
        SET res = 'fasel';
    ELSE
        SET res = 'Not found';
END CASE;

RETURN res;
END
//

DELIMITER ;

SELECT
    caseTestLong('foo') AS foo,
    caseTestLong('bla') AS bla,
    caseTestLong('bar') AS bar;