How to use CASE statement in a where clause.

Problem: How to change where clause based on a condition.

Solution:

CASE (Transact-SQL), SQL server 2008 or higher.

Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

·         The simple CASE expression compares an expression to a set of simple expressions to determine the result.

·         The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Example:

In this article I am going to show an example how to do a changing where clause, based on another column.

In this query I am doing a case based on the company_id to search the employee type column, this makes it as parameter in the where clause to search conditionally.

SELECT  [EmployeeID]

      ,[Name]

      ,[PhoneNumber]

      ,[EmployeeType]

      ,[Company_Id]

  FROM [DB].[dbo].[Employees]

  WHERE EmployeeType= CASE   WHEN  Company_ID IS NULL THEN 'General employee'

  WHEN  Company_ID  IS NOT NULL THEN 'unkown' END

OUTPUT:

Below is the execution plan: