Skip to content

Instantly share code, notes, and snippets.

@jotbe
Created July 6, 2011 10:12
Show Gist options
  • Save jotbe/1066958 to your computer and use it in GitHub Desktop.
Save jotbe/1066958 to your computer and use it in GitHub Desktop.
SQL function that returns CASE results in a short form
/**
* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment