CASE STATEMENT IN WHERE CLAUSE

Question

CASE STATEMENT IN WHERE CLAUSE

Viewing 4 posts - 16 through 18 (of 18 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Thanx again Sergiy. Now let's no go in the datatype problems for now. We'll wait for him to complain about an intermitant slow query. Then we'll tell him about the comvert_implicit operator .

Graham-214561 SSC Enthusiast Points: 195 More actions · Answer 2

From a performance point of view you would probably be better off having 2 stored procs, one for quantity and one for amount

drahmani Newbie Points: 1 More actions · Answer 3

ishaan99 (11/13/2006)
SELECT col1, col2, col3, col4, col5, AL1.amount, AL1.quantity
from tab , tab2 AL1
WHERE tab.id = AL1.id
andname like '%bnb%'
and id = 100
AND CASE
WHEN @custom_value='D' then
(AL1.amount between @threshold_value1 and @threshold_value2)
ELSE
(AL1.quantity between @threshold_value1 and @threshold_value2)
END

This is exactly what I want to do EXCEPT that it would be something like :

WHEN @custom_value='D' then

(AL1.amount IN ('100','200') )

I get an error because of the IN. Any ideas why, anyone?

Thanks!

GSquared SSC Guru Points: 260824 More actions · Answer 4

Graham (11/14/2006)
From a performance point of view you would probably be better off having 2 stored procs, one for quantity and one for amount

If they are separate, yep.

Or if you build a temp table in the calling proc, and call sub-procs to fill it up, and then filter it. But that can get really complex really fast.

- 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