CODEWITHSUNDEEP
sqlsql-serverstored-proceduresinner-join
Santiago Díaz
Santiago Díaz

Reputation: 29

JOIN with table only if parameter condition is met

I need to do a join with table B only if a parameter @param is not null or if it has a specific value.

I don't want to add IF/ELSE because the store is really big for doing that.

My store return over 50 parameters and makes joins with 10 tables.

I just want to skip one of those joins if a declared @param condition is meet.

I hope someone can help me. Thanks in advance!

UPDATE

DECLARE @param INT = NULL;

SELECT 
    /* HERE GOES A LOOT OF PARAMETERS */
FROM 
    T0 T
    INNER JOIN  T1 B ON T.attr1 = B.attr1
    LEFT JOIN   T2 TFD ON T.attr2 = TFD.attr2
    LEFT JOIN   T3 TTT ON (TTT.attr3 = TFD.attr3)
    INNER JOIN  T4 U ON B.attr4 = U.attr5 AND U.attr6 = @attr6
    -- The following commented section is the one that i want to skip if @param is NULL
    /*LEFT JOIN @tvp P ON  (
                            (P.attr11= TFD.attr9 AND T.attr8 = P.attr8 AND P.attr7 = 1) OR 
                            (P.attr11 = TFD.attr9 AND T.attr8 = P.attr8 AND P.attr7 = 0 AND B.attr4 = @attr5) OR 
                            (TFD.attr9 IS NULL AND P.attr8 = T.attr8)
                                    )*/
    LEFT JOIN   T4 FTB ON FTB.attr1 = B.attr1
    LEFT JOIN   T6 S ON S.attr10 = T.attr10
    LEFT JOIN   @splittedElementsIds SETI ON SETI.item = T.attr9
    WHERE
        (@attr9 IS NULL OR T.attr9 = SETI.item)
    GROUP BY
        /* LOOTS OF GROUP BY PARAMETERS */

Upvotes: 0

Views: 6133

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

A SQL query returns a fixed set of columns. The join in question -- because it is optional -- would not seem to be returning any columns in the query. Hence, I presume it is only being used for filtering.

If so, I might recommend using exists instead:

select . . .
from . . .
where @param <> 'true' or exists (select 1 from b where b.? = x.?)

Upvotes: 0

ferec
ferec

Reputation: 41

Use the @param condition in ON clause, here is an example:

select * from A
  left join B on A.id = B.id and @param1 = 'X'
  left join C on A.id = C.id and @param2 = 'Y'
  etc...

Upvotes: 3

Reza
Reza

Reputation: 19843

There are different solutions:

1 - You can create your sql statement dynamically by concating strings and run it by sp_executesql

2 - Using Union all as below

Assume t2 is table you wan to join conditionally, and @param is what you want to do condition based on

;With query as 
(Select * from t1
 inner join t3
 inner join t3)

Select * from query
Where @param = 1

union all 

Select * from query
inner join t2
Where @param = 2

Upvotes: 0

Related Questions