December 10, 2012 at 9:46 am
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.
December 10, 2012 at 9:50 am
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
December 10, 2012 at 9:57 am
Nevermind... missed part of the requirements and had bad code posted... GSquared has it covered...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 2:28 pm
Or:
SELECT col1,col2,col3 from table p WHERE P.HOSPITAL = @HOSPITAL and p.finclass= case when @finclass = 1 then @FinclassCategory else finclass end
December 11, 2012 at 6:49 am
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 on googles mail service
December 11, 2012 at 7:50 am
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
December 11, 2012 at 7:54 am
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
December 11, 2012 at 9:08 am
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 on googles mail service
December 11, 2012 at 9:22 am
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
December 11, 2012 at 12:04 pm
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
December 11, 2012 at 12:22 pm
In that case, try the version I posted, with the OR clause in it. Case will do the same thing, but can result in even more complex execution plans (thus, often, even slower).
- 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply