Created
July 6, 2011 10:12
-
-
Save jotbe/1066958 to your computer and use it in GitHub Desktop.
SQL function that returns CASE results in a short form
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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