Discussion Locked

Exploring the various uses of CASE

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

While you've probably used the CASE keyword to create a column, it has many more uses. This week's SQL Server newsletter offers a variety of uses for CASE. Can you think of any other uses for the CASE keyword than the ones outlined in the tip?

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

6 replies

by bob.monahon

Hello,
Re: Your comment that a query with CASE in the SELECT block must repeat it in the GROUP BY and ORDER BY blocks.

Here's a better solution, using an imbedded query. It eliminates the reptition of the CASE statement, which would become a maintenance nightmare over time.

SELECT * FROM (
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
) AS title_list
GROUP BY Range, Title
ORDER BY Range, Title

by baileyg

I would suggest that the example implicitly describes why people don't use CASE in the data layer and that in itself it is a contrived example.

Forgive me if this comment appears to be negative, but business logic does not live in a data layer for me... Java or .Net anyone?

by gunnymike

I find the use of CASE inside stored procedures to be extremely benificial. The most common challenge we face is integrating a new presentation layer that must communicate with legacy data.

I'd much rather have logic like this in one place rather than two. I have found that 'messaging data' in the presentation layer is very cumbersome. I would much rather talk to SQL and have the results come back in such a way that all I have to do in the presentation layer is serve up the data.

by mattohare

Heck, I thought this was going to be about some Computer Aided Software Engineering tool for writing stored procedures and user-defined functions. *blush*

by artful

Well, I guess I owe you a case of beer, just in case you misunderstood my use of CASE.

More seriously, I would love to do a piece about CASE technology, but I think that the SQL Tips column is not the place for it (word-count constraints). However, you have motivated me to propose a more ambitious and lengthy article to the powers that be.

Arthur

by artful

Reader Merrick Fonnesbeck pointed out that I neglected to provide an example of using the proposed function. I thought that I had, and I'll take a lame stab at blaming my editor. No I won't! It's my fault and the egg is on my face.

Now that the responsibility for this is cleared up, here is an example call. It assumes a table called the_table, with one varchar column called the_value, containing the values supplied in the tip.

SELECT TOP 100 PERCENT the_value, isnumeric(RIGHT(the_value, 1)) AS Expr1
FROM dbo.the_table
ORDER BY the_value, isnumeric(RIGHT(the_value, 1))

To phrase it more generically, you can call any scalar UDF anywhere that you could name a column. In this case, the UDF is called twice, once in the SELECT clause and once in the ORDER BY clause.

Arthur

This discussion is closed to new replies.