CASE statement [T-SQL]

Selects an execution path based on multiple cases.

Syntax

Specify a value expression

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

Specify a search condition

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

Remarks

Using a value expression
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.
Using a search condition
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
```

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

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
The CASE statement is part of Language Feature P002 (Computational completeness). However, the ANSI/ISO 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 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 GROUPO.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 a search condition to generate a message about product quantity within the sample database.

CREATE PROCEDURE DBA.StockLevel( @product_ID INTEGER ) AS
BEGIN
  DECLARe @qty INTEGER
  SELECT Quantity INTO @qty FROM GROUPO.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;