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:

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:

It is also possible to specify multiple When statements:

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: