store another table name in a column

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)