using Case get values from different table in sql server

Question

i need results based on a condition with out using dynamic sql

like when @param1=10 then i need all rows details from table1

else i need details from table2

i had tried something like below but it does not give the result

declare @param1 int=10;
select * from 

case when @param1=10 then table1

else table2 end

Note : i know i can use if condition like below

if @param1=10 
select * from table1
else
select * from table2

but in my case the query is very lengthy so i don't want to replace all query once again

so i hope i can just do something with case with out replacing all query again.

please help me to solve the problem

Answer 1

You can place a UNION ALL as a subquery, and then build the rest of your query around that:

SELECT
    * --TODO - Columns
FROM
    (SELECT * FROM tableA WHERE @Param=10
     UNION ALL
     SELECT * FROM TableB WHERE @Param<>10) t
WHERE /* other conditions here */

---

As an aside - if you have two tables that contain the same "types" of rows, such that you're wanting to interchange them in queries, it may be an indication that your data model is broken - what should have been modelled as a attribute has instead become embedded in your table names. E.g. rather than having FemaleEmployees and MaleEmployees tables, it ought to be a single Employees table with a Gender column.

Answer 2

One option to avoid dynamic SQL is a union:

select * from table1 where @param1 = 10
union all
select * from table2 where @param1 <> 10

Answer 3

If both the tables have some unique id to join, you can use something like

SELECT CASE WHEN @param1 = 10 then t1.C1
ELSE t2.C1
END as C
FROM table1 t1
INNER JOIN table2 t2 on 
t1.Id = t2.Id

try this, but you need to check the performance of your query..

Declare @t1 Table(id int , Name nvarchar(MAX))
Declare @t2 Table(id int , Name nvarchar(MAX))
Declare @t int = 10

insert into @t1 values (1,'Jhon')
insert into @t1 values (2, 'Jhon2')

insert into @t2 values (3, 'Rahul')
insert into @t2 values (4, 'Rahul2')

Select distinct Case when @t = 10 then t1.Name else t2.Name end as Name
from @t1 t1 cross join @t2 t2 

another approach

with CTE_t1 as (Select * from table1 where @param1 = 10), 
    CTE_t2 as (Select * from table2 where @param1 != 10),
    CTE_t3 as (Select * from CTE_t1 union all Select * from CTE_t2)

    Select * -- you can use your 200 line of code here only once
    from CTE_t3