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")