Using CASE in a WHERE clause

Question

Post reply

Using CASE in a WHERE clause

keymoo

SSCrazy

Points: 2975
More actions
December 3, 2010 at 5:23 am

#229359

Hi,
I have the following sample data:
create table #trades (tradeId int NOT NULL, parentTradeId int NULL )
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(161,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(162,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(163,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(164,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(165,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(166,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(167,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(168,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(169,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(170,NULL)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(171,165)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(172,167)
INSERT INTO #trades ([tradeId],[parentTradeId])VALUES(173,NULL)
I want to create a stored procedure that takes a char(1) parameter. The parameter can take the following values: 'A', 'O', 'R'.
'A' means return all rows, 'O' means return rows where parentTradeId is NOT NULL, 'R' means return rows where parentTradeId IS NULL. Possible to convert to a single query? I've tried a few queries without much success.
Thanks.
December 3, 2010 at 6:43 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

select *

from dbo.MyTable

where @Parameter = 'A'

or ParentTradeID is not null and @Parameter = 'O'

or ParentTradeID is null and @Parameter = 'R';

Wouldn't that do it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Cant you just use IF blocks ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

GSquared SSC Guru Points: 260824 More actions · Answer 3

Sachin Nandanwar (12/3/2010)
Cant you just use IF blocks ?

Why would you bother? Use the Where clause. That's what it's there for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

GSquared (12/3/2010)
select * from dbo.MyTable where @Parameter = 'A' or (ParentTradeID is not null and @Parameter = 'O')
or (ParentTradeID is null and @Parameter = 'R');
Wouldn't that do it?

Hey Gus, reckon you're still bleary-eyed after your 20th anniversary 😛 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

keymoo SSCrazy Points: 2975 More actions · Answer 5

Sachin Nandanwar (12/3/2010)
Cant you just use IF blocks ?

Yes, I could use IF blocks... 😉

keymoo SSCrazy Points: 2975 More actions · Answer 6

GSquared (12/3/2010)
select * from dbo.MyTable where @Parameter = 'A' or ParentTradeID is not null and @Parameter = 'O'
or ParentTradeID is null and @Parameter = 'R';
Wouldn't that do it?

Of course, thanks. My brain just isn't functioning today.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Chris Morris-439714 (12/3/2010)
GSquared (12/3/2010)
select * from dbo.MyTable where @Parameter = 'A' or (ParentTradeID is not null and @Parameter = 'O')
or (ParentTradeID is null and @Parameter = 'R');
Wouldn't that do it?
Hey Gus, reckon you're still bleary-eyed after your 20th anniversary 😛 😛

No. The parentheses may make it more readable, or may not, but they don't do anything functional. Try a sample with and without. They're necessary when combining OR blocks, not when both operations are part of an AND block.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

GSquared (12/3/2010)
Chris Morris-439714 (12/3/2010)
GSquared (12/3/2010)
select * from dbo.MyTable where @Parameter = 'A' or (ParentTradeID is not null and @Parameter = 'O')
or (ParentTradeID is null and @Parameter = 'R');
Wouldn't that do it?
Hey Gus, reckon you're still bleary-eyed after your 20th anniversary 😛 😛
No. The parentheses may make it more readable, or may not, but they don't do anything functional. Try a sample with and without. They're necessary when combining OR blocks, not when both operations are part of an AND block.

:blush: Oh my word...think, then write. Smacks back of own head.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandippani SSC Eights! Points: 957 More actions · Answer 9

declare @opt char

set @opt='R' -- Set your required parameter value

select * from #trades

WHERE

(

CASE

WHEN ((@opt = 'A' AND parentTradeId is null) or (@opt = 'A' AND parentTradeId is not null)) THEN 1

WHEN @opt = 'O' AND parentTradeId is not null THEN 1

WHEN @opt = 'R' AND parentTradeId is null THEN 1

ELSE 0

END

) = 1

Try this.....