Using the name of a table contained in a field of another table?

Question

I have a table (MyTable) with varchar columns named SourceID and ReferenceID where SourceID contains a table name and ReferenceID contains a key value for the source table.

Is it possible in a query to refer to the source table's record assuming I know the name of the key field that matches the value in RefrenceID?

Sample:

SELECT
    *
FROM
    MyTable as MT
INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID

This doesn't work, obviously. But is there a way to do this as opposed to dynamically building the SQL statement using the values of SourceID and ReferenceID and then executing the query? More specifically, is there a way to make MT.SourceID in the example above be usable within the JOIN?

Any and all help is greatly appreciated. :)

EDIT:

I found this article: http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

And it has a trick like follows:

select
  E.EmployeeName, coalesce(s.store,o.office) as Location
from
  Employees E
left outer join 
  Stores S on ...
left outer join
  Offices O on ...
where
  O.Office is not null OR S.Store is not null

The WHERE clause simply helps the outer joins behave like inner joins. This would work well enough if there were a limited number of table options and they all had unique keys. This gets me part of the way there but but I really want is to have the table names in the joins to be based on a field in Employees.

I haven't given up yet.

Answer 1

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.