SQL Select - return as one

Question

I have a select query. I want to replace that select query with another select if that returns no rows.

For instance lets say I have:

Select * from Temp2
if (@@rowcount=0)
 select * from Temp1

At the c# end, I retrieve it as a dataset. So if no rows are returned, it would do another select. But this select would be in Tables[1] not Tables[0].

So essentially, What I want to do is to replace the first select results in the sql stored proc with that of the last if @@rowcount = 0. I am sure this can be done. I want a tidy solution.

I can do something like this:

if ((select count(ID) from Temp2) =0)
  select * from Temp1
else
  select * from Temp2

but I have 3 selects. I would like to do it with max 2 selects because my select statement is more complex than the trivial example given and I dislike repeat the same select twice (e.g. select on Temp2).

Thanks

Answer 1

select * from Temp2
UNION ALL
select * from Temp1
where not exists (select * from Temp2)

Answer 2

Perhaps a table variable?

declare @result table({column defs})

insert @result
select (..query 1...)

if(@@rowcount = 0)
  insert @result
  select (..query 2...)

select * from @result