CASE statement [T-SQL]

Selects an execution path based on multiple cases.

Syntax 1

CASE value-expression
WHEN [ constant | NULL ] THEN statement-list ...
[ WHEN [ constant | NULL ] THEN statement-list ] ...
[ ELSE statement-list ]
END

Syntax 2

CASE
WHEN [ search-condition | NULL] THEN statement-list ...
[ WHEN [ search-condition | NULL] THEN statement-list ] ...
[ ELSE statement-list ]
END

Remarks

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.

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

CASE statement is different from CASE expression

Do not confuse the syntax of the CASE statement with that of the CASE expression. See CASE expressions.

Permissions

Side effects

Standards and compatibility

SQL/2008 The CASE statement is part of language feature P002 (Computational completeness) of the SQL/2008 standard. The SQL standard requires END CASE to terminate the CASE statement, rather than END alone.
Transact-SQL Compatible with Adaptive Server Enterprise.

Example

The following procedure using a case statement classifies the products listed in the Products table of the SQL Anywhere sample database into one of shirt, hat, shorts, or unknown.

CREATE PROCEDURE DBA.ProductType( @product_ID INTEGER,@TYPE CHAR(10) OUTPUT ) AS
BEGIN
  DECLARE @prod_name CHAR(20)
  SELECT Name INTO @prod_name FROM Products
    WHERE ID = @product_ID
  IF @prod_name
   = 'Tee Shirt'
    SET @TYPE = 'Shirt'
  ELSE IF @prod_name
   = 'Sweatshirt'
    SET @TYPE = 'Shirt'
  ELSE IF @prod_name
   = 'Baseball Cap'
    SET @TYPE = 'Hat'
  ELSE IF @prod_name
   = 'Visor'
    SET @TYPE = 'Hat'
  ELSE IF @prod_name
   = 'Shorts'
    SET @TYPE = 'Shorts'
  ELSE
    SET @TYPE = 'UNKNOWN'
END;

The following example uses Syntax 2 to generate a message about product quantity within the SQL Anywhere sample database.

CREATE PROCEDURE DBA.StockLevel( @product_ID INTEGER ) AS
BEGIN
  DECLARe @qty INTEGER
  SELECT Quantity INTO @qty FROM Products
    WHERE ID = @product_ID
  IF @qty < 30
    MESSAGE 'Order Stock' TO CLIENT
  ELSE IF @qty > 100
    MESSAGE 'Overstocked' TO CLIENT
  ELSE
    MESSAGE 'Sufficient stock on hand' TO CLIENT
END;

Discuss this page in DocCommentXchange.