An If statement in the Where clause
-
Hi
I have the below stored procedure which brings back a list of news items. Some of which have the PublishOnIntra set to true and some set to false. If PublishOnIntra is set to true then the news item can be returned for all users. However I have a master user that wishes to see all news items regardless of the PublishOnIntra setting.
Question is how do I use the @ReadOnly setting, which tells me if the current user is a normal or master user, to make the PublishOnIntra = 1 part of the Where clause? That is, if a master user then the Where clause would be:
WHERE StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
CREATE PROCEDURE spGetNews
@ProjectID Int,
@ReadOnly bitAS
SELECT MediaNewsId, MediaNewsHeadline, MediaNewsReleaseDate, MediaNewsBody
FROM CC_MediaNews
WHERE PublishOnIntra = 1 AND StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
ORDER BY MediaNewsHeadlineAny help is much appreciated.
Thanks
Brendan
-
CREATE PROCEDURE spGetNews
@ProjectID Int,
@ReadOnly bit
AS
set nocount on
if @readonly=1
begin
SELECT MediaNewsId, MediaNewsHeadline, MediaNewsReleaseDate, MediaNewsBody
FROM CC_MediaNews
WHERE StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
ORDER BY MediaNewsHeadline
end
if @readonly=0
begin
SELECT MediaNewsId, MediaNewsHeadline, MediaNewsReleaseDate, MediaNewsBody
FROM CC_MediaNews
WHERE PublishOnIntra = 1 AND StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
ORDER BY MediaNewsHeadline
end
GO
MVDBA
-
Mike
Thanks for the reply. I thought about doing it this way, but thought there might be a more efficient way of doing it in the Where clause somehow.
Something along the lines of:
WHERE (if @readonly=1 then 'PublishOnIntra = 1 AND') StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
Brendan
-
Even though you can do something like this to accomplish in one statement, it is not cost effective
WHERE StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID AND ((@ReadOnly =0 AND PublishOnIntra=0) OR (@readonly=1 AND 'PublishOnIntra = 1))
However, When you use OR clause it is not SARG-able (Any indexes on those fields are no used). If you have enough indexes on other fields you can consider this option also.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
SELECT MediaNewsId, MediaNewsHeadline, MediaNewsReleaseDate, MediaNewsBody
FROM CC_MediaNews
WHERE (PublishOnIntra = 1 OR @READONLY=1) AND StoryStatus = 4 AND MediaNewsReleaseDate <= GetDate() AND Project = @ProjectID
ORDER BY MediaNewsHeadline
i think is what Preethiviraj Kulasingham was saying
MVDBA
-
Thanks for all your help everyone.
Got the query to function properly by declaring a variable and building the string based on values passed to the procedure.
But the answers have helped me solve a similar problem I was having.
Thanks again all.
Brendan
-
Is that correct about fields in an OR statement not being SARG-able. I just tried it on a simple table and got an index seek.
Can anyone comment?
-
if you use WHERE (PublishOnIntra = 1 OR @READONLY=1) that should be SARG proof
as i'm sure this is passed as WHERE (PublishOnIntra = 1 OR 1=1)
MVDBA
-
Maybe
WHERE (@READONLY=1 or PublishOnIntra = 1)
is better. This way, the condition "PublishOnIntra = 1" is probably ignored when @READONLY equals 1. But I am not sure....
-
If you use (@READONLY=1 or PublishOnIntra = 1) in the where clause, that clause may not be SARGable. But however, it depeneds on the statistics and you can never be sure. However, If you have another field which is SARGable you can depend on that.
I have faaced this problem and you can find the material on this at
http://www.sql-server-performance.com/pk_or_clause.asp
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Interesting link....
Do we know that the column PublishOnIntra is included in an index? If it isn't, I still think that my suggestion can be useful. But it probably is included in an index...
-
My guess is that it's not in an index, since it seems to be a bit datatype. (referred to as to be set to 'true' and the variable is also bit)
At least not in an index by itself, since the density would be pretty low with only two possible values.
/Kenneth
-
SELECT MediaNewsId, MediaNewsHeadline, MediaNewsReleaseDate, MediaNewsBody
FROM CC_MediaNews
WHERE PublishOnIntra = Case when @readonly = 1 then 1 else PublishOnIntra end
and StoryStatus = 4
AND MediaNewsReleaseDate <= GetDate()
AND Project = @ProjectID
ORDER BY MediaNewsHeadline
The case is *sometimes* better than the or. It all depends on the indexes, table size, number of where clauses and so on.
If this is a high volume oltp environment I would stick to the two qrys suggested earlier. The space that procs are written to is cheap and it is easy to read. If not you can be more creative.
Gosh darn it Preethi, you and your damn "or" clauses! 😉 -Barbara
-
Both "Case" and "Or" are attractive to developpers. But I know that in Production they give trouble. My intention is not to prohibit the use of OR (or case) in where clause. I was just raising a point that the developer should be aware of when writing the code.
As Barbara said, writing two queries may be the best way in a high volume performance system where performance is a critical factor. However, I believe analysing the query execution plan is part of the development work (something like unit test). Unfortunately, at times, we may have a development environment which is not at all comparable to the production environment.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply