Contents Index CALL statement CHECKPOINT statement

ASA SQL Reference
  SQL Statements

CASE statement


Description 

Use this statement to select 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 | NULLTHEN statement-list ...
WHEN [ search-condition | NULLTHEN statement-list ] ...
ELSE statement-list ]
END CASE

Usage 

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.

For more information about the ISNULL function, see ISNULL function [Data type conversion].

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

For more information about NULL values, see Unknown Values: NULL.

CASE statement is different from CASE expression 
Do not confuse the syntax of the CASE statement with that of the CASE expression.

For information on the CASE expression, see CASE expressions.

Permissions 

None.

Side effects 

None.

See also 

BEGIN statement

Using Procedures, Triggers, and Batches

Standards and compatibility 
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.

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

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

CREATE PROCEDURE StockLevel (IN product_id INT)
BEGIN
   DECLARE qty INT;
   SELECT quantity INTO qty FROM product
   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

Contents Index CALL statement CHECKPOINT statement