Indu-649576 (9/2/2014)
twin.devil (9/2/2014)
Indu-649576 (9/2/2014)
IS there any way I can use the belowSelect X.* From TABLE X
INNER JOIN
(case when (condition) then TABLE A else TABLE B end) AS Y
ON X.Col1 = Y.Col1
you will get syntax error if you try to execute this in simple tsql. However, Yes you can do it but in dynamic tsql something like this.
Declare @Query as nvarchar(max) = 'Select X.* From TABLE X INNER JOIN '
+ (case when (condition) then 'TABLE A' else 'TABLE B' end
+ ' AS Y ON X.Col1 = Y.Col1
'
EXEC SP_EXECUTESQL @Query
[/code]
I don't think dynamic also works...Can u try with some simple tables in ur db using dynamic[/quote]
I assumed your scenario like this, on a given condition choose either TableA or TableB (Both tables contain Col1). If this is true dynamic sql does work. Following is the example to help you understand this.
USE tempdb
GO
Create table TableA (Col1 Int)
Create table TableB (Col1 Int)
Create table TableX (Col1 Int)
Declare @Query AS NVARCHAR(MAX)
Insert into TableX
Select 1 union all
select 3 union all
select 5
Insert into TableA
Select 1 union all
select 2 union all
select 3
Insert into TableB
Select 4 union all
select 5 union all
select 6
------------- For TableA
Select @Query = 'Select X.* From TableX AS X Inner join ' + (Case WHEN 1 = 1 THEN 'TableA' ELSE 'TableB' END) + ' AS Y ON Y.Col1 = X.Col1'
Print (@Query)
Exec sp_executesql @Query
------------- For TableB
Select @Query = 'Select X.* From TableX AS X Inner join ' + (Case WHEN 1 = 0 THEN 'TableA' ELSE 'TableB' END) + ' AS Y ON Y.Col1 = X.Col1'
Print (@Query)
Exec sp_executesql @Query
Drop table TableA
Drop table TableB
Drop table TableX