ASA SQL Reference
SQL Statements
Use this statement to select 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
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 expressionDo not confuse the syntax of the CASE statement with that of the CASE expression.For information on the CASE expression, see CASE expressions. |
None.
None.
Using Procedures, Triggers, and Batches
SQL/92 Persistent Stored Module feature. Adaptive Server Anywhere supports the CASE statement allowing WHEN NULL. This is a vendor extension to the SQL/92 standard.
SQL/99 Persistent Stored Module feature. Adaptive Server Anywhere supports the CASE statement allowing WHEN NULL. This is a vendor extension to the SQL/92 standard.
Sybase Not supported by Adaptive Server Enterprise.
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