Monday, 29 November 2010

SQL Server: basic functions (part 2) - The Case function


This is the second part of series of posts on basic SQL functions. In the first part we saw right, left, substring, rtrim, ltrim and len.
In this post we are going to analyse the CASE function.

The Case function
This is a very useful function which has numerous applications. It evaluates a series of conditions and returns one of multiple possible results, based on the conditions. You can see it as the If... Then... Else... End if in VBScript.
First of all, remember that the function can be a simple CASE or a searched CASE function.

The simple CASE function
SELECT ProductCode, Category =
   CASE Line
      WHEN 'V' THEN 'Vegetable'
      WHEN 'F' THEN 'Fruit'
       ELSE 'Not available'
FROM ProductTable
As you can see the function evaluates the Line field: if it contains 'V', Category will be 'Vegetable'; if it contains 'F', Category will be 'Fruit', otherwise Category will be 'Not available'.
In this case, the function evaluates the content of a field in order to fill in another - new - field (which is not actually a column of the ProductTable table).

The searched CASE function
SELECT ProductCode, 'Price Range' =
     WHEN Price < 100 THEN 'Cheap'
     WHEN Price = 100 THEN 'Average'
     WHEN Price > 100 and Price < 1000 THEN 'Expensive'
     ELSE 'Too Expensive'
FROM ProductTable
In the example, the function evaluates the Price field in order to give a product the correct Price Range.

The CASE function as If.. Then..
You can use the CASE function as If... Then... Else. As an example, you want to evaluate a field like this:
If the Price field is null then the price is 'upon request', otherwise show the price.
Let's see it in action:
SELECT ProductCode, Price =
      WHEN Price is null THEN 'Upon request'
      ELSE Price
FROM ProductTable
Nested CASE function
You can use the function in a nested way:
SELECT ProductCode, Price =
     WHEN Customer is null THEN 'Contact the sales department'
           WHEN Price < 100 THEN Price
            ELSE 'Upon request'
FROM ProductTable

The above example is just to give an idea of how you could use the CASE function in a nested way. Usually you do not need it, but I thought you should know anyway.

Where to use the CASE function
The CASE function can be used wherever you want. You can use it in update, insert, delete or select statements. You can use it in where, having, and order by clauses.
It can be used with the ELSE argument (as seen in the examples) or without it.
In my opinion, due to its flexibility, the CASE function has multiple applications and simplify data manipulation a lot.

In the next post of the series, we are going to see cast and convert.

Have a nice day, and don't forget to comment.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.