Using Case When with Leftjoin

Question

I am mounting a proc, but depending on the value of a parameter, I would like left Join to be different.
I tried the kerys below:

That works, but the else as null or even without the else, if the parameter COD_FORNECEDOR_FILTRO is null, the query returns nothing.

LEFT JOIN FORNECEDOR F
        ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
        AND F.COD_FORNECEDOR = 
        (CASE WHEN @COD_FORNECEDOR_FILTRO IS NOT NULL 
        THEN @COD_FORNECEDOR_FILTRO 
        ELSE NULL
        END)

This one doesn’t work.

CASE WHEN COD_FORNECEDOR_FILTRO IS NOT NULL 
THEN LEFT JOIN FORNECEDOR F ON F.ID_FORNECEDOR = T.ID_FORNECEDOR AND F.COD_FORNECEDOR = @COD_FORNECEDOR_FILTRO
ELSE LEFT JOIN FORNECEDOR F ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
END

The idea is that if the parameter has value, have Join with the field F.COD_FORNECEDOR if not, only with the F.ID_FORNECEDOR

I’m not doing it the way I’d like to. You can help me?

Answer 1

You can use the field itself F.COD_FORNECEDOR when the value is null.

LEFT JOIN FORNECEDOR F
        ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
        AND F.COD_FORNECEDOR = 
        (CASE WHEN @COD_FORNECEDOR_FILTRO IS NOT NULL 
        THEN @COD_FORNECEDOR_FILTRO 
        ELSE F.COD_FORNECEDOR
        END)