Case statements in T-SQL

If you’re used to working with If-Else statements in 3rd generation languages such as Java and C++ then you will find that Case statements can be useful when constructing complex queries in T-SQL. Very handy when there are a lot of requirements about how and where data should be extracted from.

An example:

CustomerContactAddress contains all address records for your existing customers.
In this scenario, you want to:
Select all the ContactAddress records.
If the Customer City is not a New Zealand city, select ‘OVERSEAS’ as City.

SELECT
cca.StreetNumber,
cca.StreetName,
cca.Suburb,
CASE
WHEN cca.City not in (select CityName from NewZealandCities) THEN 'OVERSEAS'
ELSE cca.City
END as City
cca.State,
cca.Country
FROM
CustomerContactAddress cca
-- WHERE
-- some requirement

Nested CASE statements can also be useful:

If City not from New Zealand, check if city is from Australia in which case use ‘AUS’, otherwise ‘OVERSEAS’.

select
case when city not in (select city from nzcities) then
case when city in (select city from auscities) then 'AUS' else 'OVERSEAS'
END
END as City
FROM
table