Thanks for the valuable info

But i need this way

but this gives error

whts the correct way?

If exists (select 1 from #1_TEMP where Status= 'd') and exists (select 1 from #1_TEMP where Status= 'i')

begin

select * from #1_TEMP

end

UNION ALL

If exists (select 1 from #2_TEMP where Status= 'd') and exists (select 1 from #2_TEMP where Status= 'i')

begin

select * from #2_TEMP

end

UNION ALL

If exists (select 1 from #3_TEMP where Status= 'd') and exists (select 1 from #3_TEMP where Status= 'i')

begin

select * from #3_TEMP

end

UNION ALL

If exists (select 1 from #4_TEMP where Status= 'd') and exists (select 1 from #4_TEMP where Status= 'i')

begin

select * from #4_TEMP

end

UNION ALL

If exists (select 1 from #5_TEMP where Status= 'd') and exists (select 1 from #5_TEMP where Status= 'i')

begin

select * from #5_TEMP

end

StatusReNo ItmDescQty

d* X

d2.0 XX

i2.2

i2.3

i2.4

i2.5

i2.6

i2.7

i2.9

some table can conatin only data with status ='d'

the final result shld contain the tables with data 'd' and 'i'

if it conatins only 'd' then the table shld not be unioned