title: How to join dynamic sql statement in variable with normal statement
body: <p>I have a quite complicated query which will by built up dynamically and is saved in a variable.</p>
  
  <p>As second part i have another <em>normal</em> query and i'd like to make an inner join between these both.</p>
  
  <p>To make it a little more easier here is a little example to illustrate my problem.<br>
  For this little example i used the <a href="http://msftdbprodsamples.codeplex.com/releases/view/4004" rel="nofollow noreferrer">AdventureWorks database</a>.</p>
  
  <h3>Some query built up dynamically</h3>
  
  <p>(Yes, i know there is nothing dynamic here, cause it's just an example.)</p>
  
  <pre><code>DECLARE @query AS varchar(max) ;
  
  set @query = '
  select
      HumanResources.Employee.EmployeeID
      ,HumanResources.Employee.LoginID
      ,HumanResources.Employee.Title
      ,HumanResources.EmployeeAddress.AddressID
  from
      HumanResources.Employee
      inner join HumanResources.EmployeeAddress
      on HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
  ;';
  
  EXEC (@query);
  </code></pre>
  
  <h3>The normal query i have</h3>
  
  <pre><code>select
      Person.Address.AddressID
      ,Person.Address.City
  from
      Person.Address
  </code></pre>
  
  <h3>Maybe what i'd like to have but doesn't work</h3>
  
  <pre><code>select
      @query.*
      ,Addresses.City
  from
      @query as Employees
      inner join
      (
          select
              Person.Address.AddressID
              ,Person.Address.City
          from
              Person.Address
      ) as Addresses
      on Employees.AddressID = Addresses.AddressID
  </code></pre>