Conditional JOIN in SQL Server

Question

I would like to change join the tables based on the passing parameters to a stored procedure. Based on passing parameter the join should be done.

How can I achieve it?

Answer 1

try this. I guess you need to select values from diffferent tables based on the input parameter.

DECLARE @tmpTable1 TABLE(ID INT,Name VARCHAR(5))
DECLARE @tmpTable2 TABLE(ID INT,Value INT)
DECLARE @tmpTable3 TABLE(ID INT,Value INT)

DECLARE @vchTableType VARCHAR(5) = '2'
-- Possible values 2 fro table2 and 3 for table 3

INSERT INTO @tmpTable1 VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D');

INSERT INTO @tmpTable2 VALUES
(1,100),
(2,200),
(3,300),
(4,400);

INSERT INTO @tmpTable3 VALUES
(1,10),
(2,20),
(3,30),
(4,40);

SELECT 
                     T1.ID
                    ,T1.ID
                    ,CASE @vchTableType WHEN '2' THEN T2.Value WHEN '3' THEN T3.Value ELSE -1 END Column1
FROM                @tmpTable1 T1

LEFT OUTER JOIN     @tmpTable2 T2
ON                  T1.ID = T2.ID

LEFT OUTER JOIN     @tmpTable3 T3
ON                  T1.ID = T3.ID

Answer 2

If you want to join different columns based on your condition, use this

SELECT *
FROM tblA A
JOIN tblB B
ON A.col1 =
    CASE WHEN <any condition> THEN B.col1        
   END