Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic and SQL
Expertise: Intermediate
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.

If you dont specify [expression], you must include the [Boolean expression] in the WHEN clause. This can contain any valid Boolean expression SQL Server allows:


DECLARE @TestVal int
SET @TestVal = 5

SELECT
	CASE
		WHEN @TestVal <=3 THEN 'Top 3'
		ELSE 'Other'
	END
Jason Rein
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap