CASE
A CASE statement in SQL Script evaluates a list of conditions and returns one of multiple possible result expressions. The CASE statement has two valid formats.
Syntax 1
Use the <valueExpression> syntax to evaluate an expression once and then find a matching value. The WHEN clauses are evaluated in order and the first match is used.
CASE <valueExpression>
WHEN <valueExpression> THEN <statements>
[…]
[ELSE <statements>]
END AS <new_column_name>
Syntax 2
Use the <conditionalExpression> syntax to evaluate a series of tests like an IF/THEN/ELSEIF/ELSE. The WHEN clauses are evaluated in order and the first match is used.
CASE
WHEN <conditionalExpression> THEN <statements>
[…]
[ELSE <statements>]
END AS <new_column_name>
Remark
There can be zero or more statements in the area indicated by <statements>.
Examples
PROCEDURE get_month_name(OUT month_name VARCHAR)
BEGIN
CASE MONTH(CURRENT_DATE() )
WHEN 1 THEN
SET month_name = 'JAN';
WHEN 2 THEN
SET month_name = 'FEB';
WHEN 3 THEN
SET month_name = 'MAR';
...
WHEN 11 THEN
SET month_name = 'NOV';
WHEN 12 THEN
SET month_name = 'DEC';
END CASE;
END
PROCEDURE get_duration(IN seconds INTEGER, OUT result VARCHAR)
BEGIN
CASE
WHEN seconds < 60 THEN
SET result = CAST (
CONCAT(seconds, ' seconds') AS VARCHAR);
WHEN seconds < 60*60 THEN
SET result = CAST (
CONCAT(seconds/60, ' minutes') AS VARCHAR);
ELSE
SET result = CAST (
CONCAT(seconds/3600, ' hours') AS VARCHAR);
END CASE;
END