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?
-
2How different would the JOIN be ? Do you want to join a different table or on (one or more) different field(s) ?deroby– deroby2011-12-15 09:28:27 +00:00Commented Dec 15, 2011 at 9:28
Add a comment
|
2 Answers
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
Comments
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
