Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically choosing inner or outer join Expand / Collapse
Author
Message
Posted Monday, June 16, 2008 7:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 2,664, Visits: 888
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
Post #517535
Posted Monday, June 16, 2008 8:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604
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.



Post #517599
Posted Monday, June 16, 2008 8:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #517601
Posted Monday, June 16, 2008 11:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
You could always do a LEFT JOIN and just specify a filter based on your @innerjoin parameter

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)
Post #517761
Posted Tuesday, June 17, 2008 12:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 2,664, Visits: 888
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!
Post #518495
Posted Wednesday, June 18, 2008 1:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604
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



Post #518784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse