ASP Kitchen: SQL Server Articles: How to SQL Server's Transact SQL Case function

How to SQL Server's Transact SQL Case function

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

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

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

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

Useful Development Tools

ASP Documentation Tool™

Automatically creates technical documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework applications written in C# or VB.NET (including ASP.NET). Documentation for SQL Server 7/2000/2005 databases and C#/VB.NET components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats. Additional support for ASP.NET web applications. A useful alternative to NDoc!

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0, 2000 and 2005 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships, Jobs and DTS Packages.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript search engines and server-side ASP search engines and ASP.NET search engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

Text Workbench

Text Workbench is a file search and replacement utility for text files and Microsoft Office documents. Make rapid file replacements on multiple files and folders full of files. Advanced replacement options include regular expressions support. It even works on remote file systems via FTP. A Regular Expression Laboratory allows advanced pattern matching and replacement expressions to be built and tested. This great utility will make your everyday development tasks much easier!

Download Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation).

Indexing Service Companion

The Indexing Service Companion is a utility that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from any remote website and also from ODBC compliant databases. As such it can be used as a low cost alternative to Sharepoint's Search Services.

View Product Documentation (119K ZIP file).     Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

ASP Spell Check

ASPSpellCheck is the easy way to add spell checking capabilities to your ASP or ASP.NET websites, Intranets and web applications. The utility allows you to add spell checking capabilities to any HTML text field or rich content editing text box. It works with all common web browsers, and there are no components or databases to install on the server.

Read ASPSpellCheck Review.     View ASPSpellCheck Examples.     Download Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation).

Author details

Brett Burridge has worked as a web developer since 1997 and has developed web applications for a range of corporations, start up busiensses and educational establishments.

Brett is presently employed as an Internet developer and technical writer through his own company, Winnersh Triangle Web Solutions Limited. The company produces a number of innovative products, including a range of software documentation tools, which include the ASP Documentation Tool™, the .NET Documentation Tool for VB.NET and C#, and the SQL Server Documentation Tool. Other products include The Website Utility, which functions as a website error checker, search engine optimizer and ASP/ASP.NET search engine builder application.

As well as the ASPAlliance, Brett has written articles for Ariadne.ac.uk, ASPToday, the software documentation portal www.softwaredocumentation.info, and has contributed recipes to the ASP.NET Developer's Cookbook.    links

Outside web development, Brett is interested in travelling (here are my travel logs from New York, Hong Kong and Tokyo), digital photography (here's my photo gallery), tropical fishkeeping and collecting contemporary works of art by artists such as Doug Hyde.

Contact Brett by emailing

ASP Kitchen: SQL Server Articles: How to SQL Server's Transact SQL Case function