General discussion

Locked

Exploring the various uses of CASE

By MaryWeilage Editor ·
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

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Simpler use of CASE in GROUP BY/ORDER BY

by bob.monahon In reply to Exploring the various use ...

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

Collapse -

The CASE for CASE

by baileyg In reply to Exploring the various use ...

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?

Collapse -

The CASE for CASE

by GunnyMike In reply to The CASE for CASE

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.

Collapse -

Overloaded CASE

by mattohare In reply to Exploring the various use ...

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

Collapse -

Case in Case

by artful In reply to Overloaded CASE

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

Collapse -

Example Omitted

by artful In reply to Exploring the various use ...

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

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums