Thursday, June 21, 2007

SQL CASE Function

SQL Case evaluates a list of conditions and returns one possible result expressions.

CASE has two formats:
1. Simple CASE Function - Compares an expression to determine the result.
2. Searched CASE Function - Evaluates a set of Boolean expressions to determine the result.


CASE Syntax
1. Simple CASE function:
CASE input_expression
WHEN when_expression THEN Result
ELSE result_expression
END

2. Searched CASE function:
CASE
WHEN Boolean_expression THEN Result
ELSE result_expression
END

1. Simple CASE Function
Evaluates input_expression and find the match with when_expression. If found, it will return the Result and if not found, it will return the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Type varchar(20)
SET @Type = 'Programming'

SELECT
   CASE @Type
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com'
      WHEN 'Programming' THEN 'programmingschools.blogspot.com'
      WHEN 'Travel' THEN 'travelyourself.blogspot.com'
      ELSE 'Not yet categorized'
      END
Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized



2.Searched CASE Function
Evaluates Boolean_expression for each WHEN clause and returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, SQL Server returns the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.


DECLARE @Price integer
SET @Price = (20-9)

SELECT
   CASE
      WHEN @Price IS NULL THEN 'Not yet priced'
      WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'
      WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'
      ELSE 'Expensive book!'
   END
Value = Reasonable Price

If SET @Price = (30-1), then return Value = Expensive book!

4 comments:

Jonathan said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

difff said...
This comment has been removed by the author.
difff said...

SQL Reports there are great SQL lessons. Suitable for beginners on SQL selects.

Sql tutorial

kreatx said...

nice an example usage inside an udf would complete the topic