Hi

It is not clear what you are asking, posting ddl and your statements would
help.

John

I dont know your scenario

Try this

create table #test(id int)
create table #test1(id int)
create table #test2(id int)

insert into #test values (1)
insert into #test1 values (2)
insert into #test2 values (3)

declare @flag bit
set @flag = 0

/* if flag is = 0 then will union first two tables else it will union
all three tables */

select * from #test
union all
select * from #test1
union all
select * from #test2 where 1 =  case @flag  when 0 then  0 else 1 end

Thanks and Regards
Amish Shah
http://shahamishm.blogspot.com

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.  Temporal data should use ISO-8601
formats.  Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications.  It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

I think better than a case is an IF:

if @idontneed3tables = 0
begin
select .. from a union all select ... from b
end
else
begin
select .. from a union all select ... from b select .. from c
end

that guarantees optimal plan regardless of anything

You can create two views and select from one of them depending on
something. However, are you aware of partitioning? Shouldn't you store
all similar data in one table?