SQL Union not working when one table is empty

Solution 1:

This statement is simply untrue:

once I use UNION to combine it with the data from the empty table the resulting dataset will be empty as well, even though it contained data from the first two datasets before.

If one of the components of a UNION is empty, then you will still get the results from the other tables.

Note: If one of the tables does not exist -- which is quite different from being empty -- then the query returns an error, but not an empty result.

I will note that in general you should be using UNION ALL instead of UNION, to avoid the overhead of removing duplicate values. And your code is wide open to SQL injection attacks and to unexpected syntax errors. I would question whether your application can be designed so you don't have to use dynamic SQL for this purpose.