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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s