Expertise: Intermediate
Language: Visual Basic and SQL
May 9, 2003
Use the CASE Statement in a SQL SELECT Clause
SQL Server provides a mechanism for returning different values in a SELECT clause based on Boolean conditions: the CASE statement. This statement resembles Visual Basics Select Case statement.
The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator. The syntax is:
CASE [expression]
WHEN [value | Boolean expression] THEN [return value]
[ELSE [return value]]
END
The [expression] is optional and contains a table column or a variable. When you specify [expression] directly after the CASE, you must populate the [value] parameter in the WHEN clause:
DECLARE @TestVal int
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
SQL Server compares this value to the expression and when the values match, it returns the THEN clauses [return value]. If none of the WHEN clauses equates to true, SQL Server returns the [return value] in the optional ELSE clause. If the ELSE clause is omitted and no value is matched, NULL is returned.
It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com. Already a member?
To become a member of DevX.com create your Member Profile by completing the form below. Membership is free!
If you have a hot tip and we publish it, we'll pay you. However, due to accounting overhead we no longer pay $10 for a single tip submission. You must accumulate 10 acceptable tips to receive payment. Be sure to include a clear explanation of what the technique does and why it's useful. If it includes code, limit it to 20 lines if possible. Submit your tip here.
