CASE statement

Selects an execution path based on multiple cases.

Syntax 1

CASE value-expression
WHEN [ constant | NULL ] THEN statement-list ...
[ WHEN [ constant | NULL ] THEN statement-list ] ...
[ ELSE statement-list ]
END [ CASE ]

Syntax 2

CASE
WHEN [ search-condition | NULL] THEN statement-list ...
[ WHEN [ search-condition | NULL] THEN statement-list ] ...
[ ELSE statement-list ]
END [ CASE ]

Remarks

Syntax 1 The CASE statement is a control statement that allows you to choose a list of SQL statements to execute based on the value of an expression. The value-expression is an expression that takes on a single value, which may be a string, a number, a date, or other SQL data type. If a WHEN clause exists for the value of value-expression, the statement-list in the WHEN clause is executed. If no appropriate WHEN clause exists, and an ELSE clause exists, the statement-list in the ELSE clause is executed. Execution resumes at the first statement after the END CASE.
If the value-expression can be null, use the ISNULL function to replace the NULL value-expression with a different expression.

Syntax 2 With this form, the statements are executed for the first satisfied search-condition in the CASE statement. The ELSE clause is executed if none of the search-conditions are met.

If the expression can be NULL, use the following syntax for the first search-condition:

WHEN search-condition IS NULL THEN statement-list

CASE statement is different from CASE expression

Do not confuse the syntax of the CASE statement with that of the CASE expression. See CASE expressions.

Permissions

Side effects

Standards and compatibility

SQL/2008 The CASE statement is part of language feature P002 (Computational completeness) of the SQL/2008 standard. The use of END alone, rather than END CASE, is a vendor extension.
Transact-SQL The CASE statement is supported by Adaptive Server Enterprise. See CASE statement [T-SQL].

Example

The following procedure using a case statement classifies the products listed in the Products table of the SQL Anywhere sample database into one of shirt, hat, shorts, or unknown.

CREATE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
   DECLARE prod_name CHAR(20);
   SELECT Name INTO prod_name FROM Products
   WHERE ID = product_ID;
   CASE prod_name
   WHEN 'Tee Shirt' THEN
      SET type = 'Shirt'
   WHEN 'Sweatshirt' THEN
      SET type = 'Shirt'
   WHEN 'Baseball Cap' THEN
      SET type = 'Hat'
   WHEN 'Visor' THEN
      SET type = 'Hat'
   WHEN 'Shorts' THEN
      SET type = 'Shorts'
   ELSE
      SET type = 'UNKNOWN'
   END CASE;
END;

The following example uses Syntax 2 to generate a message about product quantity within the SQL Anywhere sample database.

CREATE PROCEDURE StockLevel (IN product_ID INT)
BEGIN
   DECLARE qty INT;
   SELECT Quantity INTO qty FROM Products
   WHERE ID = product_ID;
   CASE
   WHEN qty < 30 THEN
      MESSAGE 'Order Stock' TO CLIENT;
   WHEN qty > 100 THEN
      MESSAGE 'Overstocked' TO CLIENT;
   ELSE
      MESSAGE 'Sufficient stock on hand' TO CLIENT;
   END CASE;
END;

Discuss this page in DocCommentXchange.