Let's say that you have the same table that exists in two different schemas -
A.my_table
B.my_table
...and you want to write a query with a parameter so you can select from one schema or the other. Without resorting to dynamic SQL.
One possible solution is to write a UNION query that will deliver the data from one schema or the other, like this:
DECLARE @schema sysname
SET @schema = 'A'
SELECT *
FROM A.my_table
WHERE @schema = 'A'
UNION ALL
SELECT *
FROM B.my_table
WHERE @schema = 'B'
Here we are using the ALL keyword to make sure that we retrieve all records; normally a UNION will only retrieve unique records.
Of course, the DDL for both tables must match, or the UNION will fail; the number of columns must be the same, and the data types must be compatible.
This idea was actually applied to an SSRS report - for which, the data source must be either T-SQL text or a stored procedure.
