CASE Expression in Mircorsot-T SQL

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Simple CASE expression

The syntax of the simple CASE expression is:

CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END 

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

• Allows only an equality check.
• Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.
• Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
• If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

The following example uses the CASE expression to change the display of product line categories to make them more understandable.

SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;

Searched CASE expression

The syntax of the searched CASE expression is:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

The searched CASE expression operates by evaluating each boolean expression, if the expression returns true, the expression in the THEN clause will be returned. 

• Evaluates, in the order specified, Boolean_expression for each WHEN clause.
   
• Returns result_expression of the first Boolean_expression that evaluates to TRUE.
   
• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.
   

The following example displays the list price as a text comment based on the price range for a product.

SELECT   ProductNumber, Name, 'Price Range' =
      CASE
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;