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

I have a table called 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 row assuming I know the name of the key column 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 MyTable.SourceID in the example above be usable within the JOIN?
Further considerations

I found this article, 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 constraints. 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 column in Employees.

I haven't given up yet. Any and all help is greatly appreciated.

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.

StackExchange Database Administrators Q#5865, answer score: 4