SQL Server - JOIN ON conditional statement

Question

I have a specific problem where the JOIN ON can be based on one of two statements:

LEFT JOIN acc_seminar.t_Seminar_Gebühr semg ON 
        CASE 
            WHEN @Bool = 1
                THEN ss1.TNOrder = semg.SemG_TN OR (ss1.TNOrder > @MaxTN AND semg.SemG_TN = @MaxTN)
            ELSE
                semg.SemG_TN = 1
        END

As you can see, if a variable has a value equal to 1 then it should left join on one statement or join on the other if the variable value is not equal to 1.

As far as Googling tells me, something like this is not possible in SQL because CASE returns a value, not a statement. How could I change this to make it logically work as shown above?

Answer 1

CASE is an expression in T-SQL; there is no Case Statement in the language.

What you need here is just "normal" Boolean Logic:

LEFT JOIN acc_seminar.t_Seminar_Gebühr semg ON (@Bool = 1
                                           AND  ss1.TNOrder = semg.SemG_TN
                                            OR  (ss1.TNOrder > @MaxTN AND semg.SemG_TN = @MaxTN))
                                            OR (@Bool = AND semg.SemG_TN = 1)

Note that Joins like this can be bad for performance due to bad query plan caching. As a result using Dynamic SQL, or OPTION (RECOMPILE) can help avoid using the wrong cached plan, or recreating it each time (respectively). Personally, I prefer the dynamic approach.