SQL Server 2005 - If condition with union

Question

I have a SP that has the following algorithm.

IF <SomeCondition>
BEGIN
  SELECT * FROM TABLE1
END
ELSE
BEGIN
  SELECT * FROM TABLE2
END
--Union the above with the query below
UNION
  SELECT * FROM TABLE3

The recordset returned is EXACTLY the same. I need to do a UNION of that resultset and another query.

Is there a way to do this without having to use a temp table?

Answer 1

How about:

SELECT * FROM TABLE1 WHERE <SomeCondition>
UNION
SELECT * FROM TABLE2 WHERE NOT <SomeCondition>
UNION
SELECT * FROM TABLE3

If you're worried about evaluating twice:

DECLARE @condition bit
SET @condition = CASE WHEN <SomeCondition> THEN 1 ELSE 0 END

SELECT * FROM TABLE1 WHERE @condition = 1
UNION
SELECT * FROM TABLE2 WHERE @condition = 0
UNION
SELECT * FROM TABLE3

Answer 2

You could also use dynamic SQL if you don't mind that it isn't compiled. For example:

DECLARE @sql VARCHAR(100)
DECLARE @table VARCHAR(10)

IF <SomeCondition>
BEGIN
    SET @table = 'Table1'
END
ELSE
BEGIN
    SET @table = 'Table2'
END

SET @sql = 'SELECT * FROM ' + @table + ' UNION SELECT * FROM TABLE3'
EXEC(@sql)