title: How to use a recursive query as a subquery?
body: <p>I need to write a query that calls a recursive query many times.</p>
  
  <p>I was not able to figure out how to do. I guess I can do this by using a cursor, preparing the sql statement at run time and then use EXEC(mySQLstatement) to run it at every cursor FETCH NEXT.</p>
  
  <p>Anyway this is not the good approach.</p>
  
  <p>This is the problem (of course here it is simplified and I leave only the necessary columns to express myself):
  I have a tree of customers (a hierarchy) and for every customer there are some contacts defined.</p>
  
  <p>The CUSTOMERS table containts an ID_CUSTOMER field and an ID_PARENT_CUSTOMER field
  the CUSTOMER_CONTACTS table contains an ID_CUSTOMER field and an ID_CONTACT field.</p>
  
  <p>With this query (it works) i am able to get all the contacts for customer 308 and all the contacts for its sub-customers:</p>
  
  <pre><code>with [CTE] as (
      select ID_CUSTOMER from CUSTOMERS c where c.ID_CUSTOMER = 308
      union all
      select c.ID_CUSTOMER from [CTE] p, CUSTOMERS c 
          where c.ID_PARENT_CUSTOMER = p.ID_CUSTOMER
  )
  select ID_CUSTOMER into #Customer308AndSubCustomers from [CTE]
  
  select 308 as ParentCustomer, ID_CUSTOMER, ID_CONTACT,  from CUSTOMER_CONTACTS
  WHERE ID_CUSTOMER IN (select * from #Customer308AndSubCustomers)
  drop table #Customer308AndSubCustomers
  </code></pre>
  
  <p>But I would like to have in a single query the same for ALL THE CUSTOMERS, not only for 308. So this is why I was suggesting to use a cursor so I can reuse the above statement and just use a variable instead of 308.</p>
  
  <p>But can you suggest a better query?</p>