case join statement

I have parameter @.cona.
If @.cona=1 then
............
from lokStanje l INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN
LEFT JOIN .............
If @.cona=0 then--I don't need INNER JOIN:
............
from lokStanje l
LEFT JOIN .............
I don't won't to write 2 queries, one for @.cona=0 and one for @.cona=1
because query is quite large.
I would like to know if it's possible to somehow combine @.cona in my query,
something like:
............
from lokStanje l case when @.cona=1 then INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN end
LEFT JOIN .............
Thank you for your answer,
SimonWith the least code possible:
............
from lokStanje l INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN
OR @.cona=0
LEFT JOIN .............
However, this might not result in the optimal query plan. If you are coding
to get the best performance, having two separate statements (maybe even in 2
separate stored procedures) is the better solution, as the best query plan
will be used in every case.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:uTzxdOCPFHA.3336@.TK2MSFTNGP09.phx.gbl...
>I have parameter @.cona.
> If @.cona=1 then
> ............
> from lokStanje l INNER JOIN
> (objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
> Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
> ON l.LOK_OZN=Lc.LOK_OZN
> LEFT JOIN .............
>
> If @.cona=0 then--I don't need INNER JOIN:
> ............
> from lokStanje l
> LEFT JOIN .............
> I don't won't to write 2 queries, one for @.cona=0 and one for @.cona=1
> because query is quite large.
> I would like to know if it's possible to somehow combine @.cona in my
> query, something like:
> ............
> from lokStanje l case when @.cona=1 then INNER JOIN
> (objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
> Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
> ON l.LOK_OZN=Lc.LOK_OZN end
> LEFT JOIN .............
> Thank you for your answer,
> Simon
>