CASE Statement in situations where several conditions need to be evaluated. The CASE Statement evaluated a list of conditions and returns one of the possible results. CASE expressions can be used in SQL anywhere an expression can be used. You can use a CASE Statement when there are more than two conditions that check a common variable for different value. 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.
1) Simple CASE expressions.
Simple CASE expressions construct, a variable or an expression is compared with the Boolean expression in each WHEN clause. If any of these expressions evaluate to TRUE, then the expression specified with the THEN clause is executed. If the expression does not evaluate to TRUE, the expression with the ELSE statement is executed.
The syntax of the Simple CASE Statement is:
WHEN expression1 THEN expression1 [[WHENexpression2 THEN expression2] [...] ] [ELSEexpressionN]
Example Simple CASE expression is shown below.
Declare @empval int
set @empval = 3
when 1 then 'First'
when 2 then 'Second'
when 3 then 'Third'
when 4 then 'Four'
The result is displayed below with Simple CASE expression.
2) Searched CASE expressions.
A Searched CASE expression is the resultant expression that is executed when the Boolean expression evaluates to TRUE. This can be constant, a column name, a function, a query, or any combination of arithmetic, bit-wise, and String operators. The simple CASE expressions check only for equivalent values and can’t contain Boolean expressions.
The basic syntax for searched CASE expressions is shown below:
CASE WHEN Boolean_expression1 THAN expression1
[[WHEN Boolean_expression2 THEN expression2] [...] ]
Example Searched CASE expression is shown below.
Declare @STDval int
Set @STDval =5
When @STDval <=3 then 'TOP 3'
The result is displayed below with Searched CASE expression.