April 21, 2008 at 5:38 am
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?
April 21, 2008 at 5:41 am
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.
April 21, 2008 at 6:36 am
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.
April 21, 2008 at 6:40 am
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.
April 21, 2008 at 6:45 am
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
April 21, 2008 at 6:52 am
Sorry, my bad! It was still running another IF statement in my sproc...
Thanks Ryan, that's brilliant 😎
April 21, 2008 at 7:28 am
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
April 21, 2008 at 7:40 am
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.
April 21, 2008 at 7:45 am
Hi Roy,
Could I ask why it's better to use the CASE statment?
April 21, 2008 at 7:45 am
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
April 21, 2008 at 7:52 am
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
April 21, 2008 at 8:17 am
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
April 21, 2008 at 11:52 am
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
April 21, 2008 at 1:38 pm
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
April 28, 2008 at 6:56 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply