Selects execution path based on multiple cases.
CASE value-expression …WHEN [ constant | NULL ] THEN statement-list … … [ WHEN [ constant | NULL ] THEN statement-list ] … …ELSE statement-list … END
This procedure using a CASE statement 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
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. 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.
The ANSI standard allows two forms of CASE statements.
Although 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, see the CASE statement Syntax 2 in “CASE statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D).
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” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures.
None
None
