Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2006-01-05 : 16:27:40
|
| (Edited because I'm on crack and used "full join" where I meant "inner join")I'm starting the very daunting process of moderninzing a very complex query that is currently using dynamic SQL. The query has about 18 parameters that can affect what tables it needs to join against. Generally, they fall into two categories: lists of critera that need to be met (CSV that gets parsed into a table variable in the current version, XML that gets openXML'd into a table variable in the new version), and flags for whether a particular criteria is required or not (now using dynamic SQL to determine between a "join" and a "left join").Am I going to be able to do this without dynamic SQL? I'm seeing two problems:- When XML is passed in and parsed into a table variable, I need to inner join against it. However, if XML is not sent in, those criteria don't apply. Doing a full join against my table variable is going to get me no records- Similarly, when the flag is set to require a inner join on a given criteria, it'll work fine. But how to optionally do an outer join?Both of these come down to needing to parameterize my joins so that they only happen conditionally, and can be conditionally used as inner or outer joins.This has to be a common problem. Is there any way to achieve what I'm looking for without dynamic SQL or writing one SP for every join permutation (18! is quite a few SP's, but I suppose they could be programmatically generated).Thanks-b |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-05 : 16:43:12
|
| When you say "full join", I hope you don't mean a FULL OUTER JOIN ????If you mean INNER JOIN, then why not always use a LEFT JOIN and then, in your WHERE clause, filter out NULL values as needed based on your criteria (thus, making it an INNER JOIN).i.e.,SELECT * FROM ALEFT OUTER JOIN B ON ...WHERE (@Use_Left_Join = 1 OR B Is Not NULL) |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-01-05 : 16:57:54
|
| Er, yeah, "inner join". Not full join, thank god (!That's a good idea, but I'm concerned about the performance impact of having to actually join against any possible table that could be used, only to throw out those that aren't needed. For some tables and basic flags, it totally worksWorse, I'm still not sure that could be done without using dynamic SQL for the where clause; there could possibly be tens to hundreds of criteria, and it will vary based on the XML and flags that are input. I'm trying to come up with a simplified but still illustrative example, since I know it's hard to talk about this without concrete examples.Cheers-b |
|
|
|
|
|
|
|
|
Conditional joins?