Using IF after WHERE in SQL Select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using IF after WHERE in SQL Select

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2008-09-16 : 14:48:33

Newbie question

I'd like to select the price from a table based on 3 conditions (min, max, equal).
There will be at least one condition that is not null.

select mid from Price
where 

if @min is not null then
mid >= @min

if @max is not null then
and mid <= @max

if @equal is not null then
and mid= @equal

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-16 : 15:09:09

try


select mid from Price
where coalesce(@min,Mid) >= Mid
and coalesce(@Max,Mid) <= Mid
and coalesce(@Equal,Mid) = Mid

It's been a long day, I reversed the order, the code above should work now.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bfoster
Starting Member

30 Posts

Posted - 2008-09-16 : 15:16:37

I guess I have a different interpretation of your question. My query will not produce the same results as the one above. Here's my suggestion which may or may not be what you're looking for.

select mid from Price
where (@min is null or mid >= @min)
and (@max is null or mid <= @max)
and (@equal is null or mid = @equal)

daman
Yak Posting Veteran

72 Posts

Posted - 2008-09-16 : 15:57:58

bfoster got what I wanted to do. Thanks you both for your time, gentlemen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources