CASE statement

Description

Selects 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

Examples

Example 1

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

Usage

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.

The ANSI standard allows two forms of CASE statements. While 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 Adaptive Server Anywhere, see CASE statement Syntax 2 in Adaptive Server Anywhere SQL Reference.

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 “Expressions”.