CASE Expressions in MS SQL Server

A switch statement allows a variable to be tested for equality against a list of values. Each value is called a case, and the variable being switched on is checked for each switch case. We are familiar how to use switch in c#. By using CASE we can achieve this in SQL server also.

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

There are two different way we can form CASE expressions.

• Simple CASE expressions
  A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A 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.
  DECLARE @TestVal INT
SET @TestVal = 3
  SELECT
CASE @TestVal
  WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
  Result Third
• Searched CASE expressions
  A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:
  DECLARE @TestVal INT
SET @TestVal = 50
  SELECT
CASE
WHEN @TestVal <=10 THEN 'Input is less than 10'
WHEN @TestVal >10 && @TestVal <=20 THEN 'Input is greater than 10 and less than 20'
WHEN @TestVal >20 && @TestVal <=30 THEN 'Input is greater than 20 and less than 30'
WHEN @TestVal >30 && @TestVal <=40 THEN 'Input is greater than 30 and less than 40'
ELSE 'Input is greater than 40 and less than or equal to 50'
END
  Result Input is grater than 40 and less than or equal 50