# CASE (TSQL Function)

Returns the first result in the list for which the condition is true.

Two syntaxes can be used:

- Simple CASE syntax
- Searched CASE syntax

Simple CASE syntax is used when an expression must be compared for equality with a list of values.

Searched CASE syntax is used for more complex conditions.

### Syntax

Simple CASE syntax:

```
CASE expression
WHEN value_1 THEN result_1 [...WHEN value_n THEN result_n]
[ELSE result_else]
END
```

Searched CASE syntax:

```
CASE
WHEN condition_1 THEN result_1 [...WHEN condition_n THEN result_n]
[ELSE result_else]
END
```

### Arguments

- expression
- an expression of any type, which will be compared for equality with
*value_1*…*value_n*. - value_1…value_n
- is a list of expressions of any type.
- condition_1…condition_n
- are condition expressions of type
`bool`

. - result_1…result_n
- is a list of expressions of any type. From left to right, if a condition is true, the corresponding result will be returned.
- result_else
- optional clause. If no condition is true, this result is returned. It can be of any type.

### Return Types

Returns the type of the result with the highest type precedence.

### Remarks

If ELSE clause doesn’t exist and no condition is true, NULL is returned.

The simple CASE syntax is internally rewritten as a searched CASE syntax:

```
CASE a WHEN c1 THEN r1 WHEN c2 THEN r2 ELSE r3 END is internally rewritten as:
CASE WHEN a=c1 THEN r1 WHEN a=c2 THEN r2 ELSE r3 END
```

This means that the expression

`a`

is evaluated multiple times.

So, you should not use non-deterministic functions such as`RAND()`

within`CASE`

, as explained for`NULLIF`

.

MS SQL Server has the same issue.

### Examples

```
DECLARE @a INT = 2;
-- Simple CASE syntax
PRINT CASE @a WHEN 1 THEN 'Apples' WHEN 2 THEN 'Oranges' WHEN 3 THEN 'Bananas' ELSE 'Other fruit' END;
-- Searched CASE syntax
PRINT CASE WHEN @a=1 THEN 'Apples' WHEN @a=2 THEN 'Oranges' WHEN @a=3 THEN 'Bananas' ELSE 'Other fruit' END;
PRINT CASE
WHEN @a<=1 THEN 'Apples'
WHEN @a=2 THEN 'Oranges'
WHEN @a>=3 AND @a<10 THEN 'Bananas'
ELSE 'Other fruit'
END;
```

The result is:

```
Oranges
Oranges
Oranges
```