SQL Server CASE Statement and CASE WHEN Examples

Many a times there’s a need to create a derived column in an output, based on some condition. The condition is similar to the typical if construct we use if many programming languages.

In SQL scripting, we can use Case expressions or Case Statements as you may call them, to create a derived column based on a condition.

Also read about Nested Case Statements in SQL Server.

Let us first execute a sample database creation script that we will be using in our examples:

Use Master
go
Create Database Case_Demo
go
Use Case_Demo
go
CREATE TABLE EMP
       (EMPNO Integer,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer)
go
INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK', 7902, '12/17/80', 800, NULL, 20)
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698, '5/20/81', 1600, 300, 30)
INSERT INTO EMP VALUES
        (7521, 'WARD', 'SALESMAN', 7698, '5/22/81', 1250, 500, 30)
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839, '4/2/81', 2975, NULL, 20)
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698, '9/28/81', 1250, 1400, 30)
INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839, '5/1/81', 2850, NULL, 30)
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839, '6/9/81', 2450, NULL, 10)
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',  7566, '12/9/82', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL, '11/17/81', 5000, NULL, 10)
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698, '9/8/1981',1500,    0, 30)
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK', 7788, '1/12/83', 1100, NULL, 20)
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK', 7698,  '12/3/81', 950, NULL, 30)
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST', 7566, '12/3/81', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',  7782, '1/23/82', 1300, NULL, 10)
go

There are two types of Case expressions:

1. Case acting as a Switch Case construct.
2. Case acting like an If…Else If….Else construct.

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-Case


Else 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:

Case 
    When Boolean Condition 1 Then Statement1 or Expression 1
    When Boolean Condition 2 Then Statement2 or Expression 2
    When Boolean Condition 3 Then Statement3 or Expression 3
    ........................................................
    ........................................................
    When Boolean Condition m Then Statement m    or Expression m
    Else
        Statement n or Expression n
End

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.

select ename, sal, case 
    when sal >= 5000 then 'A'
    when sal >= 2500 then 'B'
    else
    'C'
           end as "Salary Class"
From Emp

Image 4: Case-If-Else

Note: 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.

Let us see an example

select ename, sal, case 
    when sal >= 3000 then 'B'
    when sal >= 5000 then 'A'
    else
    'C'
           end as "Salary Class"
From Emp

Image 5: Case-Logically-Wrong

Note: 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.

The following example will depict this point.

select ename, job, sal, case 
    when job = 'clerk' and sal < 1000 then '1'
    when job = 'clerk' and sal > 1000 then '2'
    when job = 'manager' and sal > 2900 then '3'
               end as "Bonus Grade"
From Emp

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.