SQL Server - Understanding full power of CASE Expressions

Many programming languages like VB6 support IIF function which is used to return a value based on a particular expression/condition. In SQL Server the equivalent is to make use of CASE Expression, which is used to evaluate many conditions and return a single value. The following examples will give you some ideas on how to use CASE expression effectively declare @t table(emp_id int, emp_name