title: Dynamic SQL with variable number of table joins
body: <p>My table <code>icdClm</code> is a junction table between a table with person demographics and another table that lists various codes of interest related to that person.  I'd like to be able to join these tables a variable number of times.  I'm not interested in the other table involved in the relationship because of simplicity's sake at this point.  I want a <code>while loop</code> to help generate the query (if there is a better way I'm open to a different implementation.)</p>
  
  <p>Below is the desired output if the query were to run twice (two times for the two derived tables).</p>
  
  <pre><code>select distinct x1.patid
  from
  (
  select ic.patid
      from icdClm as ic
      where ic.icd like '123%'
  ) x1 inner join 
  (
  select ic.patid
      from icdClm as ic
      where ic.icd like '456%'
  )x2 on x1.patid=x2.patid
  inner join 
  (
  select ic.patid
      from icdClm as ic
      where ic.icd like '789%'
  
  ) x3 on x1.patid=x3.patid
  </code></pre>
  
  <p>notice that they alias for the derived tables increases by 1 each time.  Now for my code.  This code does not generate an error, but I left it running for over 10 minutes and no results had yet been returned.  Eventually, I would like to make another stored procedure dependent on this one that asks you the number of derived tables you want and then fills in the <code>where ic.icd like '123%'</code> with parameters inputted from the user, but first things first: what is not working with the below query?</p>
  
  <pre><code>declare 
  @x int
  ,@y int
  ,@sql nvarchar(1000)
  
  select 
  @x=1
  ,@y=3
  ,@sql =
  'select distinct x1.patid
  from
  (
  select ic.patid
      from icdClm as ic
      where ic.icd like ''123%''
  ) x1'
  while @x &lt; @y
  begin;
  set @sql=@sql+ 
  'inner join 
  (
      select ic.pati
      from icdClm as ic
      where ic.icd like ''456%''
  ) x1 on x'+CAST(@y as CHAR(10))+'.patid=x1.patid'
  set @y=@y+1
  end;
  print @sql
  </code></pre>