Switch Case Construct

Let us see an example of Case acting as a Switch Case construct.

The syntax is as follows:

Case Expression or Column Name
When Value1 Then Statement1 or Expression 1
When Value2 Then Statement2 or Expression 2
When Value3 Then Statement3 or Expression 3
.......................................................
.......................................................
When Value m Then Statement m or Expression m
Else
Statement n or Expression n
End

Note: The Value above will be the actual value from a column, i.e. string or numeric or date value.

The Statement can be a string , numeric or date value. Expression can be either a derived value such as [Column Name] * 12 or using a T-SQL function Lower([Column Name])
Let us see an example to make things clearer.

Let us display the employee name, job and job_points for each record. Now job_points will be a derived column based on a condition that says - If job is of Analyst then job_points will be 100, if it is clerk then 200, else for any other job type it will be 300.

select ename, job, case job
when 'Analyst' then 100
when 'Clerk' then 200
else 300
end as "Job Points"
from emp

Image 1: Case-As-Switch-CaseElse part is optional. If it is not mentioned, then NULL will be returned for the default values.
Let us see the same

select ename, job, case job
when 'Analyst' then 100
when 'Clerk' then 200
end as "Job Points"
from emp

Image 2: Case-Without Else
All the statements mentioned in the then clause should be of the same data type.

select ename, job, case job
when 'Analyst' then 100
when 'Clerk' then 'Two Hundred'
end as "Job Points"
from emp

The above query throws an error since at first the when clause is 100 i.e. a numeric value, but the next when clause has 'Two Hundred' as a value of string type.

The error message is as follows:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Two Hundred' to data type int.
As I mentioned earlier, All the statements mentioned in the then clause should be of the same data type. So to fix this error, we need to enclose 100 in single quotes to make it a Varchar value.

select ename, job, case job
when 'Analyst' then '100'
when 'Clerk' then 'Two Hundred'
end as "Job Points"
from emp

Now we will get the desired output -

Image 3: Case-After-Rectification

IF ELSE Construct

Let us now see how to use the Case expression as an If…Else If….Else construct.
The syntax will be as follows:

Note:In this syntax, after the CASE keyword there is no Expression or Column Name. We directly have a When clause. The When clause will have a Boolean condition in which the column name or expression will be included.

Let us see an example of this.

Let us create a query which will show employee name, salary and salary class. If the salary is greater than or equal to 5000 then salary class will be A, if it is between 2500 and 4999 then it will be B and for remaining salaries, values will be C.

Image 4: Case-If-ElseNote: The sequence of Boolean conditions will matter a lot. Whichever condition is true, its statement or expression gets executed and it comes out of the CASE expression for that record.

So if we swap the first two conditions in the above example, the output will go haywire.

Image 5: Case-Logically-WrongNote: To avoid such logical errors, it will be better to avoid relational operators like >,>=. Instead use the between operator, mention the exact range and after doing this, if the conditions are given in any sequence, the output will be logically correct. Alternatively if you are going to continue with >,>= operators, then strictly all the conditions should be descending or all should be ascending.

We can mention multiple conditions in each WHEN clause using logical and/or operators.

Image 6-Case-Multiple-Conditions-In-When
We explored the SQL Server CASE statement and also saw the CASE WHEN example. In the next article, we will explore Nested Case Statements in SQL Server. and use CASE in Having, Order By and UPDATE statements.