June 16, 2008 at 7:24 am
Hi,
Today a developer bring me a problem.
He need to rewrite a SP and add some parameters to filter the results.
The SP is like:
[SP_OLD] @param1
as
select tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and now needs to evaluate like:
[SP_NEW] @param1, @param2
as
set @innerjoin = dosomethingwith(param1,param2)
if @innerjoin = false
select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and ta.param2 = @param2
else
select *
from tableA ta
inner join tableB tb on tb.idA=ta.idA
inner join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).
I managed to do it using IF..ELSE but i wonder if exists a better way. Can i use CASE in the JOIN clause?
The query is pretty big and uses old fashioned joins in the WHERE clause (=, =* and *=).
I will use the better answer to rewrite lots of scripts in a old-big-bad BD designed app.
Thanks in advance.
Jean C. Bulinckx
June 16, 2008 at 8:41 am
You can't use CASE to determine whether the join will be LEFT JOIN or INNER JOIN if that's what you were asking.
You probably could use additional conditions in WHERE clause to achieve similar effect, but IMHO your solution with IF and two separate blocks of code is better. See the code below for how that could be done (the idea is tested and works, but you may need to modify it for your environment - you didn't explain much about the @innerjoin parameter, so I wrote it as if it was varchar with values "Yes" or "No").
SELECT *
FROM tableA ta
LEFT JOIN tableB tb ON tb.idA=ta.idA
LEFT JOIN tableC tc ON tc.idB=tb.idB
WHERE ta.param1 = @param1
AND ta.param2 = @param2
AND (tb.idA=ta.idA OR @innerjoin = 'No')
AND (tc.idB=tb.idB OR @innerjoin = 'No')
Edit: To be correct, the way I wrote it, any other value for @innerjoin than 'No' will cause that the query will perform as inner joined.
June 16, 2008 at 8:49 am
jcb (6/16/2008)
I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).
Write 3 stored procs. The app always calls the one and passed the param to say left or inner join. Based on that the proc calls one of 2 other procedures. No change to the dll needed.
I must admit, I'm curious about what would need the join type changing at run time. Reports?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2008 at 11:35 am
You could always do a LEFT JOIN and just specify a filter based on your @innerjoin parameter
[font="Courier New"]select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and (@innerjoin = false or ta.param2 = @param2)
AND (@innerjoin = false or tb.idA is not null)
AND (@innerjoin = false or tc.idB is not null)[/font]
June 17, 2008 at 12:30 pm
Nice work guys!
Vladan,
yep, I wondered about a case in the where clause. Why not?
GilaMonster,
Great! I can keep the original SP and make 2 news SPs in that context.
It´s pretty good because the app is a monster and is hard to determine if are others calls to that SP over thousands of SPs, triggers, ASP, DLLs, jobs...
PS: This call is used to validate something in a long and complex chain of rules (from my view point its like one of Tom's trap to catch Jerry).
Michael Earl,
Nice and direct like the Vladan solution.
That was my first approach. Unfortunelly the real world query uses joins in the where clause and i got a run time error.
Just because u cannot join the same tables "twice" while the same tables are in a outer join. I tested u query and it worked. Maybe its a limitation only for "old fashioned" joins.
Thanks for all u time guys!
June 18, 2008 at 1:00 am
jcb,
this is one of my favorite links - it discusses how to apply various search criteria, and mentions many possible solutions, their advantages, drawbacks and performance. Maybe it will help you to choose the best way.
-> Dynamic Search Conditions in T-SQL
Many other interesting articles by the same author can be accessed from Erland Sommarskog's home page
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply