SQL Case

SQL Case evaluates a list of conditions and returns one possible result expressions.

CASE has two formats:
1. Simple CASE Function - Compares an expression to determine the result.
2. Searched CASE Function - Evaluates a set of Boolean expressions to determine the result.

CASE Syntax
1. Simple CASE function:
CASE input_expression
WHEN when_expression THEN Result
ELSE result_expression
END

2. Searched CASE function:
CASE
WHEN Boolean_expression THEN Result
ELSE result_expression
END

1. Simple CASE Function
Evaluates input_expression and find the match with when_expression. If found, it will return the Result and if not found, it will return the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Type varchar(20)
SET @Type = 'Programming'

SELECT
   CASE @Type
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com'
      WHEN 'Programming' THEN 'programmingschools.blogspot.com'
      WHEN 'Travel' THEN 'travelyourself.blogspot.com'
      ELSE 'Not yet categorized'
      END
Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized



2.Searched CASE Function
Evaluates Boolean_expression for each WHEN clause and returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, SQL Server returns the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Price integer
SET @Price = (20-9)

SELECT
   CASE
      WHEN @Price IS NULL THEN 'Not yet priced'
      WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'
      WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'
      ELSE 'Expensive book!'
   END
Value = Reasonable Price

If SET @Price = (30-1), then return Value = Expensive book!