title: HierarchyID: Get all descendants for a list of parents
body: <p>I have a list of parent ids like this <code>100, 110, 120, 130</code> which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query:</p>
  
  <pre><code>WITH parent AS (
      SELECT PersonHierarchyID FROM PersonHierarchy
      WHERE PersonID = 100    
  )
  SELECT * FROM PersonHierarchy
  WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1
  </code></pre>
  
  <p>Have no idea how to do that for multiple parents. My first try was to write something like several unions, however I'm sure that there should be smarter way of doing this.</p>
  
  <pre><code>SELECT * FROM PersonHierarchy 
  WHERE PersonHierarchyID.IsDescendantOf(
      (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 100)
  ) = 1
  UNION ALL
  SELECT * FROM PersonHierarchy 
  WHERE PersonHierarchyID.IsDescendantOf(
      (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 110)
  ) = 1
  UNION ALL ...
  </code></pre>
  
  <p>P.S. Also I found such query to select list of ids which might be helpful:</p>
  
  <pre><code>SELECT * FROM (VALUES (100), (110), (120), (130)) AS Parent(ParentID)
  </code></pre>
  
  <p>To summarize, my goal is to write query which accepts array of parent IDs as a parameter and returns all their descendants in a single set.</p>