Use of Case Expression in SQL Server with Example

CASE  Expression

    The SQL Server (Transact-SQL) CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement. CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.
  You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.

SYNTAX:

CASE expression

   WHEN   value1   THEN   result1
   WHEN   value2   THEN   result2
   ...
   WHEN   value_n THEN   result_n

   ELSE  your_msg

END

EXAMPLE:

SELECT   StudentId,

CASE   RollNo

  WHEN   1   THEN   'RAVI'
  WHEN   2   THEN   'BHUSHAN'
   ....
  WHEN   100   THEN   'THAKUR'

  ELSE   'BATCH_2012-15'

END   as   StudentName

FROM   tbStudent


Note:

1. If no value/condition is found to be TRUE, then the CASE statement will return the value in the ELSE clause.
2. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
3. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
4. You can not use the CASE statement to control program flow, instead, use loops and conditional statements.