Reference Manual
   CHAPTER 9. SQL Statements     

CASE statement

Function 

Select execution path based on multiple cases.

Syntax 

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

Permissions 

None.

Side effects 

None.

See also 

BEGIN... END statement

Using Procedures, Triggers, and Batches

Description 

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. 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.

Standards and compatibility 

• SQL/92     Persistent Stored Module feature.

• Sybase     Not supported by Adaptive Server Enterprise.

Example 

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

The following procedure uses a case statement to classify the results of a query.

CREATE PROCEDURE ProductType (IN product_id INT, OUT type CHAR(10))
   BEGIN
   DECLARE prod_name CHAR(20) ;
   SELECT name INTO prod_name FROM "DBA"."product"
   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