Subquery will sometimes need to union with another table, can I put an IF?

Question

My query looks like:

SELECT *
FROM 
(
        SELECT *
        FROM Server1

        UNION

        SELECT *
        FROM Server2
)

Now, I have the options for the subquery:

1. only server 1
2. only server 2
3. both server 1 and 2

I was thinking of having a parameter in my stored procedure that would perform a bitwise operation on the parameter to tell me if I need option 1, 2, or 3.

Is this possible? Is there a better way?

Answer 1

If you have a stored procedure then putting in a parameter to make the decision is best. If you only want to have three options you could use a bit, which can have three values (0, 1, null).

But, you may want to use a byte at the minimum, so that you can have more control, in case more servers are added, but I would use null to do a join on all of them.

It is possible, as you can use an if statement in TSQL.

Answer 2

SELECT * From ServerA WHERE @Server1 = 0
UNION
SELECT * From Server2 WHERE @Server2 = 0

Does that give you an idea on how to proceed?
Also, it might try to execute all query & return no records for the query (e.g. if you pass @Server1 = 1).

You might have to look for performance, here.

EDIT: If this is not a right approach, I will appreciate if someone can explain with cons of this approach vs pros of other approach.

Answer 3

Don't use variables in the UNION to trigger if either side returns its respective query. It's save to assume that SERVER1 and SERVER2 have identical columns in order for the UNION to work...

Using IF/ELSE:

IF ... 
  SELECT * FROM SERVER1
ELSE IF ...
  SELECT * FROM SERVER2
ELSE
  SELECT *
    FROM (SELECT *
            FROM SERVER1
          UNION
          SELECT *
            FROM SERVER2)

This uses dynamic SQL:

DECLARE @SQL nvarchar(3000)

    SET @SQL = CASE
                 WHEN ... THEN
                  'SELECT * FROM SERVER1'
                 WHEN ... THEN
                  'SELECT * FROM SERVER2'
                 ELSE
                  'SELECT *
                     FROM (SELECT *
                             FROM SERVER1
                           UNION
                           SELECT *
                             FROM SERVER2)'
               END

EXEC sp_executesql @SQL