Stored Procedure case

 

Many people know that case is one of the most misused keywords in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can use case in the WHERE clause. First, let's take a look at the case syntax. In a general SELECT statement, the syntax is as follows:

 

Code
Select <mycolumnspec> =
Case
When <A> then <somethinga>
When <B> then <somethingb>
Else <somethinge>

 

In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:

 

Code
Use pubs go
Select title, 'price range' = case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End
From titles
Order by price

 

This is a typical example of case, but you can do more with case. For example, the case in the group by clause below:

Code
Select 'number of titles ', count (*) from titles
Group
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End

You can even combine these options to add an order by clause, as shown below:

Code
Use pubs go select
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End
As range, title from titles
Group
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End, Title Order
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End, title

Note: To use case in the group by block, the query statement must repeat the case block in the select block in the group by block. In addition to selecting custom fields, case is useful in many cases. If you go further, you can also obtain the grouping sorting result set that you previously thought was not possible.

From: http://hongyin163.sh103.idcwind.net/post/13.html