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
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 SQL Anywhere 11.0.1 > SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D) > CASE statement.
Do not confuse the syntax of the CASE statement with that of the CASE expression.
For information on the CASE expression, see Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Expressions.
SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.
None