Simplified WHERE clause???

Question

Post reply

Simplified WHERE clause???

Lil Ms Squirrel

SSC Veteran

Points: 277
More actions
April 21, 2008 at 5:38 am

#186813

I have a sproc that filters on the SiteNumber field of a table but I also want to give the user the option of returning all Sites if they pass -1 to the @SiteNumber variable.
At the moment, I have an IF statement that basically says
IF @SiteNumber = 1
BEGIN
SELECT blah
FROM blah
WHERE IsValid = 1
END
ELSE
BEGIN
SELECT blah
FROM blah
WHERE IsValid = 1
AND SiteNumber = @SiteNumber
END
Is there a prettier way of doing this so that I don't have to maintain two SQL statements which are identical save from part of the WHERE Clause?

Viewing 15 posts - 1 through 14 (of 14 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

1 or -1? Anyway, here's one way...

SELECT blah FROM blah WHERE IsValid = 1 AND (@SiteNumber = -1 OR SiteNumber = @SiteNumber)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Lil Ms Squirrel SSC Veteran Points: 277 More actions · Answer 2

I'm not sure I explained myself properly...

If @SiteNumber = -1 then I want to return all records but if @SiteNumber has a value of one or greater then I want to filter by that number...

The value of the SiteNumber column can never equal -1 as it's a mandatory field linked to a drop down list.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Lil Ms Squirrel (4/21/2008)
I'm not sure I explained myself properly...
If @SiteNumber = -1 then I want to return all records but if @SiteNumber has a value of one or greater then I want to filter by that number...
The value of the SiteNumber column can never equal -1 as it's a mandatory field linked to a drop down list.

Did the query I gave you not help?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Ryan's query should get you what you need.

- 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

Lil Ms Squirrel SSC Veteran Points: 277 More actions · Answer 5

Sorry, my bad! It was still running another IF statement in my sproc...

Thanks Ryan, that's brilliant

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

I would suggest you to use a Case statement over an OR clause.

SELECT blah

FROM blah

WHERE IsValid = 1

AND SiteNumber = Case When @SiteNumber = -1

Then SiteNumber

Else @Sitenumber

END

-Roy

RyanRandall SSChampion Points: 13623 More actions · Answer 7

Roy Ernest (4/21/2008)
I would suggest you to use a Case statement over an OR clause.
SELECT blah
FROM blah
WHERE IsValid = 1
AND SiteNumber = Case When @SiteNumber = -1
Then SiteNumber
Else @Sitenumber
END

Are you going to tell us why?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Lil Ms Squirrel SSC Veteran Points: 277 More actions · Answer 8

Hi Roy,

Could I ask why it's better to use the CASE statment?

GSquared SSC Guru Points: 260824 More actions · Answer 9

Roy Ernest (4/21/2008)
I would suggest you to use a Case statement over an OR clause.
SELECT blah
FROM blah
WHERE IsValid = 1
AND SiteNumber = Case When @SiteNumber = -1
Then SiteNumber
Else @Sitenumber
END

Any particular reason for that recommendation?

I tried:

declare @ID int

select @id = 1

select nodeid

from dbo.HierarchiesNodes

where nodeid =

case @id

when -1 then nodeid

else @id

end

select nodeid

from dbo.HierarchiesNodes

where nodeid = coalesce(nullif(@id, -1), nodeid)

select nodeid

from dbo.HierarchiesNodes

where (@id = -1 or nodeid = @id)

select nodeid

from dbo.HierarchiesNodes

where nodeid = @id

union all

select nodeid

from dbo.HierarchiesNodes

where @id = -1

All four had index scans (the last one had an index seek on the first select and a scan on the second).

Is there another reason to use Case instead of Or, since they seem to resolve the same way in the engine?

Edit: After further testing, the Case and Or statement take the same IO and the same CPU and total time, while the Coalesce has the same IO and less CPU and total time, and the Union version takes the least CPU and total and less Logical Reads (though the same number of Scans). So, of the four, the Union version is the most efficient, but the margin is pretty tiny on this test.

- 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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

The Basic reason why I prefer Case over OR is it is much more easier to read. Especially when there are more than one OR condition. It really makes the query cluttered. Case makes it more readable.

-Roy

GSquared SSC Guru Points: 260824 More actions · Answer 11

Roy Ernest (4/21/2008)
The Basic reason why I prefer Case over OR is it is much more easier to read. Especially when there are more than one OR condition. It really makes the query cluttered. Case makes it more readable.

That makes sense. Thanks.

- 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

DAVNovak Hall of Fame Points: 3120 More actions · Answer 12

RyanRandall (4/21/2008)
1 or -1? Anyway, here's one way...
SELECT blah FROM blah WHERE IsValid = 1 AND (@SiteNumber = -1 OR SiteNumber = @SiteNumber)

I am not sure on this, but I believe SQL Server checks both sides of the OR statement even if the first one is true.

With a case statement:

SELECT blah FROM blah WHERE IsValid = 1 AND SiteNumber = CASE WHEN @SiteNumber = -1 THEN SiteNumber ELSE @SiteNumber END

It will only do one check. If the first check is true, then it does the THEN part of the case, if it is false, then it does the ELSE part.

There are some SQL Pros on this forum that could state the validity of this explanation and I welcome them to chime in.

Dave Novak

GSquared SSC Guru Points: 260824 More actions · Answer 13

In the actual execution, yes, it stops after the first true test in the Case statement. When building the execution plan, it has to check all of them, and it picks the one that will work the worst (I'm perhaps over-simplifying), because it picks the one that will require the most work of the options presented.

For example, as per this thread, it has a choice between an index seek, if @ID is not -1, or an index scan if @ID = -1. Thus it picks an index scan, and uses that even if @ID != -1, when it actually runs the query.

So, yeah, the Case statement terminates when it finds a true condition (or returns null if it can't find any), but in this particular case, it doesn't speed up the query any. As proven by the tests I did.

- 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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 14

That means that your original IF statement is the most performing if you rarely execute the SP using a value in your variable.

Which is one of the reasons the 'Switching' SP approach has a lot of followers. It can optimize your SP (in that case SPs) differently for different inputs. So even if it is a clutter with a multitude of similar SPs you will actually get the best performance out of your server...

Regards,

Hanslindgren

Simplified WHERE clause???

Cookies on SQLServerCentral