Use of CASE statement in MS SQL Server

CASE statement is very use full in sql server if you need to show different value for different conditions. One can use it in SELECT list, WHERE clause, HAVING clause, IN list or in DELETE and UPDATE statements.

When we were upgrading one of our application we need to drop lots of old table and created new tables, to keep running the application, we created equivallent views for the dropped tables and CASE Statement proved very handy to get equivallent columns value.

The general syntax for CASE statement is:

CASE expr
WHEN epr1 THEN returnexp1
[[WHEN expr2 THEN returnexpr2] [...]]
[ELSE exprn]
END


example 1:

CASE @dayId
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wenesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
ELSE 'Wrong Day No'
END

example 2:

DECLARE @var INT
SET @var = 11
SELECT
CASE
WHEN (@var % 2 ) = 0 THEN 'Var is Even'
ELSE 'var is Odd'
END