CASE

Evaluates the expression and returns the specified value if a condition is met.

The CASE expression can be used in two ways - simple or list.

Simple expression

The simple expression evaluates the expression and returns the value specified, optionally a default value can be specified.

 CASE WHEN expression operator expression THEN return_expression [ELSE return_expression] END

List Expression

The list expression evaluates the value of an expression and measures it against another expression in the form seen below:

CASE expression
WHEN eval_expression_1 THEN return_expression
WHEN eval_expression_2 THEN return_expression
ELSE return_expression
END

Example:

DECLARE @myvar as int
set @myvar=100
PRINT 'Size of myvar:'+
CASE
WHEN @myvar<100 THEN 'Small'
WHEN @myvar>=100  AND @myvar<500 THEN 'Medium'
WHEN @myvar>=500  AND @myvar<1500 THEN 'Large'
ELSE 'Enterprise'
END

List Usage

DECLARE @myvar as int
set @myvar=1
PRINT 'MyVar :'+
CASE @myvar
WHEN 1 THEN 'Step 1'
WHEN 2 THEN 'Step 2'
WHEN 3 THEN 'Step 3'
ELSE 'Step '+@myvar
END