Optionally use a UNION from another table in T-SQL without using temporary tables or dynamic sql?

Question

I have two sql server tables with the same structure. In a stored procedure I have a Select from the first table. Occasionally I want to select from the second table as well based on a passed in parameter.

I would like a way to do this without resorting to using dynamic sql or temporary tables.

Answer 1

Pass in param = 1 to union, anything else to only return the first result set:

select field1, field2, ... from table1 where cond
union
select field1, field2, ... from table2 where cond AND param = 1

Answer 2

If they are both the exact same structure, then why not have a single table with a parameter that differentiates the two tables? At that point, it becomes a simple matter of a case statement on the parameter on which results set you receive back.

A second alternative is dual result sets. You can select multiple result sets out of a stored procedure. Then in code, you would either use DataReader.NextResult or DataSet.Tables(1) to get at the second set of data. It will then be your code's responsibility to place them into the same collection or merge the two tables.

A THIRD possibility is to utilize an IF Statement. Say, pass in an integer with the expected possible values of 1,2, 3 and then have something along this in your actual stored procedure code

if @Param = 1 Then 
    Select From Table1
if @Param = 2 THEN 
    Select From Table2
if @Param = 3 Then
   Select From Table1 Union Select From Table 2

A fourth possibility would be to have two distinct procedures one which runs a union and one which doesn't and then make it your code's responsibility to determine which one to call based on that parameter, something like:

myCommandObject.CommandText = IIf(myParamVariable = true, "StoredProc1", StoredProc2")

Answer 3

It's pretty easy.

/* Always return tableX */
select colA, colB
from tableX
union
select colA, colB
from tableY
where @parameter = 'IncludeTableY'  /* Will union with an empty set otherwise */

If this isn't immediately apparent (it often isn't), consider the examples below. The primary thing to remember is that the if the where clause evaluates to true for a row, it is returned otherwise it's discarded.

This always evaluates to true so every row is returned.

select * 
from tableX
where 1 = 1

This always evaluates to false so no rows are returned (sometimes used as a quick and dirty get-me-the-columns query).

select * 
from tableX
where 1 = 0

Answer 4

this will return values from either table, depending on if you passed a value on the parameter

select field1, field2, ... from table1 where @p1 is null
union
select field1, field2, ... from table2 where @p1 is not null

you just need to add the rest of your criteria for the where clause

Answer 5

Use a view.

CREATE view_both AS SELECT *, 1 AS source FROM table1 UNION ALL SELECT *, 2 AS source FROM table2

SELECT * FROM view_both WHERE source < @source_flag

The optimizer determines which, or both, tables to use based on source without requiring it to be indexed.