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?


    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:

  E.EmployeeName, coalesce(s.store,o.office) as Location
  Employees E
left outer join 
  Stores S on ...
left outer join
  Offices O on ...
  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.


1 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

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

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


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

  • The last two lines of your code are very close to where I ended up. I am hoping someone identifies a way to do it without the use of EXEC(). Sep 16, 2011 at 18:30
  • SP_EXECUTESQL is the only other way...how else would the query get executed?
    – Wil
    Sep 16, 2011 at 19:42
  • From SSMS in a query window or through another application. I'm looking for an "inline" solution if there is one. Sep 16, 2011 at 20:13
  • 1) PRINT the value of @sql, copy it into the query window and execute it. 2) Write a commandline app in your favorite language to execute against the database. 3) Possibly powershell, osql or something similar to execute the statements.
    – Wil
    Sep 16, 2011 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.