Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Selects an 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 [ CASE ]
CASE WHEN [ search-condition | NULL] THEN statement-list ... [ WHEN [ search-condition | NULL] THEN statement-list ] ... [ ELSE statement-list ] END [ CASE ]
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.
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
The CASE statement is part of Language Feature P002 (Computational completeness). The use of END alone, rather than END CASE, is not in the standard.
The CASE statement is supported by Adaptive Server Enterprise.
The following procedure using a case statement classifies the products listed in the Products 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 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 following example uses search conditions to generate a message about product quantity within the SQL Anywhere sample database:
CREATE PROCEDURE StockLevel (IN product_ID INT) BEGIN DECLARE qty INT; SELECT Quantity INTO qty FROM GROUPO.Products 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;