title: Hierarchical product structure
body: <p>I'm working on a report that is in pic basic(yippee) and converting it to .NET.  The report looks below in the end. The table(only showing a portion of data to keep it simple) has the ParentID(ID) and the ChildID in it and basically joins until there isn't a ChildID available(NULL). I wrote a procedure which I thought would run fine, but sits and doesn't return any type of results. SQL is not my strong point. </p>
  
  <p><strong>Report</strong></p>
  
  <pre><code>0.........  SA33028
   1........  42S0131
   1........  5S0127
   1........  6S0175
   1........  SA33023
    2.......  16S0142
    2.......  18S0103
    2.......  24S0108
    2.......  28SM0105
    2.......  S30461.AI
    2.......  S30463
     3......  S28807
     3......  S28807
    2.......  S30641
    2.......  S31134
    2.......  S9383
    2.......  SA29801
     3......  16S0116
     3......  S27798
     3......  S31170
     3......  SA30059
      4.....  38S0116
      4.....  7S0105
      4.....  S19430.145
       5....  7SM0145
        6...  7SM0145.001
        6...  7SM0145.002
       5....  S19430
      4.....  S19431
    2.......  SA30910
  </code></pre>
  
  <p><strong>Table:</strong></p>
  
  <pre><code>ParentID      ChildID
  SA33028   42S0131
  SA33028   5S0127
  SA33028   6S0175
  SA33028   79S0101
  SA33028   S11870
  SA33028   S30151
  SA33028   SA33023
  SA33028   SA33029
  SA33023   S30463
  S30463    S28807
  </code></pre>
  
  <p><strong>SQL Procedure:</strong></p>
  
  <pre><code>ALTER PROCEDURE [dbo].[getHierarchy]
  @Parent VARCHAR(20)
  
  AS
  BEGIN
  
  SET NOCOUNT ON;
  
  WITH AllParts([PSNbr], [ChildID], [ParentID])
  AS 
  (
   SELECT Child.Parent_Part AS PSNbr, 
   Child.Parent_Part AS ChildID, 
   Parent.Parent_Part AS ParentID
   FROM ps AS Child
   LEFT OUTER JOIN ps AS Parent
   ON Child.Component_Part = Parent.Parent_Part
  
   UNION ALL
  
   SELECT AllParts.PSNbr AS PSNbr,
      AllParts.ParentID AS ChildID, 
      NewParent.Component_Part AS ParentID
   FROM AllParts
   INNER JOIN PS AS NewParent
   ON AllParts.ParentID = NewParent.Parent_Part
      AND AllParts.ParentID &lt;&gt; AllParts.ChildID
   INNER JOIN PS as NewParentInfo
   ON NewParent.Component_Part = NewParentInfo.Parent_Part
  
  )
  SELECT AllParts.[PSNbr],
         AllParts.[ParentID]
  FROM AllParts
  WHERE PSNbr = @Parent;
  </code></pre>