T-SQL Select Schema

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.