Case Statement in SQL Server

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. 

Syntax:-

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Example:- 

 SELECT FirstName,LastName,
  CASE  DepartmentName 
  WHEN 'EDP' THEN 'ELETRONIC DEPARTMENT' 
  WHEN 'Testing' THEN 'TESTING DEPARTMENT'
  WHEN 'Software' THEN 'DEVELOPEMENT DEPARTMENT'
  ELSE 'DEPARTMENT ONE' END AS DEPNAME
 FROM
 dbo.Tbl_Employee