<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
CASE statement in WHERE clause
11 posts, Page 1 of 2
1
2
»»
CASE statement in WHERE clause
Rate Topic
Display Mode
Topic Options
Author
Message
Guras
Guras
Posted Monday, December 10, 2012 9:46 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:18 PM
Points: 179,
Visits: 598
I am sending a parameter @Finclass and @FinclassCategory to a sp.
I want to use the WHERE clause based on the value of these two parameters.
If @finclass = 1 and @FinclassCategory is not null then I want to add
WHERE p.finclass = @FinclassCategory
If it's not the above case I don't want to add the filter in the query
SELECT col1,col2,col3 from table p
WHERE P.HOSPITAL = @HOSPITAL
I want to add the finclass check to the above query.
Any help would be greatly appreciated.
Post #1394690
GSquared
GSquared
Posted Monday, December 10, 2012 9:50 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
Like this:
WHERE (@finclass = 1 AND p.finclass = @FinclassCategory OR @finclass != 1)
AND P.HOSPITAL = @HOSPITAL
- 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
Post #1394693
sgmunson
sgmunson
Posted Monday, December 10, 2012 9:57 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446,
Visits: 1,883
Nevermind... missed part of the requirements and had bad code posted... GSquared has it covered...
Steve
(aka sgmunson)
Weight Loss Tips
Post #1394694
jiaochunpeng
jiaochunpeng
Posted Monday, December 10, 2012 2:28 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:28 AM
Points: 7,
Visits: 3
Or:
SELECT col1,col2,col3 from table p WHERE P.HOSPITAL = @HOSPITAL and p.finclass= case when @finclass = 1 then @FinclassCategory else finclass end
Post #1394786
TheSQLGuru
TheSQLGuru
Posted Tuesday, December 11, 2012 6:49 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 3,580,
Visits: 5,122
GSquared (12/10/2012)
Like this:
WHERE (@finclass = 1 AND p.finclass = @FinclassCategory OR @finclass != 1)
AND P.HOSPITAL = @HOSPITAL
This is going to get you a BAD cached plan. No matter which value of @finclass you pass in first. The optimizer will "sniff" that value and pick the best plan for that and cache that plan. Then every time you call the query/sproc with a different (=1 or !=1) value of @finclass you will use the same plan which will likely be the polar opposite of what is optimal. You DEFINITELY need to do OPTION (RECOMPILE) on the statement. Or even better build out a parameterized dynamic sql statement to be guaranteed to get the best plan every time. Beware SQL Injection if you use dynamic sql (which is easy to avoid in this case).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1395096
GSquared
GSquared
Posted Tuesday, December 11, 2012 7:50 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
TheSQLGuru (12/11/2012)
GSquared (12/10/2012)
Like this:
WHERE (@finclass = 1 AND p.finclass = @FinclassCategory OR @finclass != 1)
AND P.HOSPITAL = @HOSPITAL
This is going to get you a BAD cached plan. No matter which value of @finclass you pass in first. The optimizer will "sniff" that value and pick the best plan for that and cache that plan. Then every time you call the query/sproc with a different (=1 or !=1) value of @finclass you will use the same plan which will likely be the polar opposite of what is optimal. You DEFINITELY need to do OPTION (RECOMPILE) on the statement. Or even better build out a parameterized dynamic sql statement to be guaranteed to get the best plan every time. Beware SQL Injection if you use dynamic sql (which is easy to avoid in this case).
That depends on the data volume being queried, the stats on it, and the frequency with which @finclass = 1. The need for it also depends on how much tolerance there is in the application for the performance hit this will cause.
Recompile is definitely an option on this, but you can't accurately say it's definitely needed. Dynamic SQL might work better than recompile, for example.
Other options include setting up two sub-procs, one that gets called by the parent proc if @finclass = 1, and the other if it isn't; building the query with a Union [All] operator; or forcing an execution plan.
But all of that depends on whether or not performance on the query is adequate to their needs. And, if not, on the data that's being queried.
- 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
Post #1395134
Guras
Guras
Posted Tuesday, December 11, 2012 7:54 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:18 PM
Points: 179,
Visits: 598
I was thinking of writing two sub proc based on the finclass value. There will be a wrapper sp that takes the finclass values and based on that I will be calling different sps
Post #1395137
TheSQLGuru
TheSQLGuru
Posted Tuesday, December 11, 2012 9:08 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 3,580,
Visits: 5,122
Guras (12/11/2012)
I was thinking of writing two sub proc based on the finclass value. There will be a wrapper sp that takes the finclass values and based on that I will be calling different sps
Another good solution that is guaranteed to get the proper plan for each condition of @finClass, as GSquared mentioned.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1395182
GSquared
GSquared
Posted Tuesday, December 11, 2012 9:22 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
Guras (12/11/2012)
I was thinking of writing two sub proc based on the finclass value. There will be a wrapper sp that takes the finclass values and based on that I will be calling different sps
That's a good option, assuming only that you won't be adding a lot more conditions to this kind of proc. Can get really messy if you end up with dozens of different sub-procs. (I've seen that, and it's not pretty.)
Keep in mind that you need to document, in each sub-proc, that it is a sub-proc, and that any modifications to it need to be reflected properly in any related sub-procs. If, for example, someone comes along six months from now and adds a new column to one of the tables that's queried, and only adds it to one of the sub-procs and not the other, that can make for some very frustrating debugging sessions.
How far you need to go on that depends on the number of options you expect to filter for. If it gets more complex than 2 or 3 sub-procs, seriously consider parameterized dynamic SQL instead of sub-procs.
- 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
Post #1395194
Guras
Guras
Posted Tuesday, December 11, 2012 12:04 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:18 PM
Points: 179,
Visits: 598
Can I take care of this with CASE clause in the WHERE query?
p.finclass = CASE WHEN @FINCLASS <> 0 THEN @FINCLASSCATEGORY
else
Just that I am stuck at what to put in the result of Else since if @finclass <> 0 then I want all the records returned.
This is a huge sp which was written years ago and therefore I did not want to write the dynamic query or spearate procs and trying to find some workaround.
Thanks
Post #1395272
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
