CASE Statement

The CASE statement is a control statement that lets you choose a list of SQL statements to execute based on the value of an expression.

Syntax

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

Examples

Example 1 – classifies the products listed in the Products table of the demo 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 "GROUPO"."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

Usage

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.

Note: The ANSI standard allows two forms of CASE statements. Although SAP Sybase IQ allows both forms, when CASE is in the predicate, for best performance you must use the form shown here.

If you require the other form (also called ANSI syntax) for compatibility with SQL Anywhere, use this syntax:

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

With this ANSI syntax 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

Attention: Do not confuse the syntax of the CASE statement with that of the CASE expression.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

None