Case Statement in SQL Server

Usage of CASE – WHEN:

This is used to derive new set of values from the given input.

It can be used in various ways depending on the business usage.

You may compare it with IF THEN ELSE and Switch statement as it will provide the same functionality.

Syntax:

CASE

            WHEN <Condition> THEN <Result1>

            WHEN <Condition> THEN <Result2>

            WHEN <Condition> THEN <Result3>

            ELSE <Result4> --Optional

END

In above syntax ELSE is optional, if we don't specify this then it will populate the value as NULL in case nothing is matched with conditions specified in WHEN clause.

Example:

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

              ELSE 'NA'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

If we see the result as per the case statement EmpName column is populated.

As EmployeeID = 5 is not satisfying any condition, name is populated as NA

Now, remove ELSE part and check what is the result.

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

As we discussed, it placed NULL for EmployeeID = 5 as it is not satisfying any condition specified in WHEN clause.