Using CASE statement in SQL Server

Updated on August 24, 2015     Kisan Patel

This tutorial will show you how to use CASE statement to check a value of the column in SQL Server?

The CASH statement is used to evaluate several conditions and returns one of multiple possible result.

SELECT CustomerID, CompanyName, 
  IsActive =
   CASE IsActive
     WHEN 1 THEN 'Yes'
     WHEN 0 THEN 'No'
   ELSE 'N/A'
   END
 FROM [NORTHWND].[dbo].[Customers]

In above case, we are trying to get all column value of the Customers and adding another column named IsActive and when Active column value of the database table is 1 (true) then setting IsActive value to ‘Yes’, when 0 (false) then ‘No’ else setting it to ‘N/A’.

Here is another example of CASE statement to check for searched condition in SQL Server.

	SELECT FirstName, 'Country' =
		CASE
			WHEN City = 'Seattle' THEN 'US'
			WHEN City = 'London' THEN 'Britain'
		ELSE 'N/A'
			END
	FROM [NORTHWND].[dbo].[Employees]

In above case, we are checking for City column, WHEN City= ‘Seattle’ then setting ‘Country’ column value to ‘US’, City = ‘London’ THEN setting ‘Country’ value to ‘Britain’ else ‘N/A’.