Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Using the Transact SQL Case Statement

The Transact SQL Case statement is a particularly useful piece of syntax to know as it can be used to solve many potentially tricky SQL Server programming tasks without resorting to more computationally intensive procedures such as using cursors, temporary tables or processing of the results set in a programming language such as Visual Basic.

Using the Case Statement in Select Queries

One such use is to create a column in a results set with differing values depending on the value of a particular column.

For example, the pubs SQL Server sample database contains a publishers table. In this table is a country column which specifies the name of the country in which the publisher is based. If you wanted to write a SQL query to return the details of the publishers plus a shipping column that would specify whether the publisher was based in the USA or overseas then the following query could be used:

select *,
case when country = 'USA' then 'Domestic' else 'Overseas' end Shipping
from publishers
order by Shipping

This query will return a value of Domestic in the Shipping column for publishers based in the USA, or Overseas for all the non-USA based publishers.

The Case statement can also be used to identify rows containing null values for particular columns. For example, the following query will show which rows contain a null value for the country column:

select *,
case when country is null then 'Yes' else 'No' end MissingCountry
from publishers

It is also possible to specify multiple When statements:

select *,
case when country = 'USA' then 'Domestic'
when country is null then 'Unknown'
else 'Overseas' end Shipping
from publishers
order by Shipping

The Case statement can also be used to specify the selection of data from different columns depending on the value of other columns. In the SQL statement below, the Address column will contain the city and state details for USA publishers, but for overseas publishers the Address column will contain the city followed by the country:

select *,
case when country = 'USA' then city + ', ' + state
when country is null then 'Unknown Address'
else city + ', ' + country end Address
from publishers

Useful Links

  • The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012