User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

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

Usage

Procedures, triggers, and batches.

Permissions

None.

Side effects

None.

See also

• Compound statements
• The chapter "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.

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