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.

  • Sure you shouldn't be asking for data modeling input, rather than help on implementing the design you've come up with? – eevar Sep 23 '11 at 19:08
  • I'm not sure I follow what you're saying. This isn't a design. I'm trying to find a way to link from table A to unknown table B using a field (or fields) of table A. I'm not convinced yet that it can't be done. – Jerry Gagnon Sep 23 '11 at 19:28
  • I'm telling you to reconsider your data model. – eevar Sep 23 '11 at 22:07
  • So far I've only used this mechanic for event recording (a log table) and have referred to the rows programmatically by building the SQL in code. I was hoping for a more "SQL" way of linking the log records to the records they refer to. – Jerry Gagnon Sep 23 '11 at 23:48
  • XY problem – eevar Sep 28 '11 at 14:18

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(). – Jerry Gagnon Sep 16 '11 at 18:30
  • SP_EXECUTESQL is the only other way...how else would the query get executed? – Wil Sep 16 '11 at 19:42
  • From SSMS in a query window or through another application. I'm looking for an "inline" solution if there is one. – Jerry Gagnon Sep 16 '11 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 '11 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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