title: Stop Recursion Once Condition Satisfied
body: <p>I am using a CTE to explode out a Bill of Materials and need to locate all those materials that have recursive components. </p>
  
  <p>What I was attempting, was to limit the number of cycles (levels) deep, by setting BOM_Level in the child node to a maximum bound: </p>
  
  <pre><code>exec        pr_sys_drop_object '#BOMExploded'
  ;with BOM
  as
  (
      select 
          Prod_Plant_CD
      ,   Demand_Plant_CD
      ,   material_cd
      ,   component_cd
      ,   component_quantity
      ,   component_quantity   AS Calculated_Component_Quantity
      ,   BOM_Level
      ,   Demand_Quantity
      ,   CONVERT(float,1)     AS Produced_Ratio
      ,   Material_CD          AS Demand_Material_CD
      from #firstLevel a
      UNION ALL
      SELECT
          b.Plant_CD as 'Prod_Plant_CD' 
      ,   a.Demand_Plant_CD
      ,   b.Material_CD
      ,   b.Component_CD
      ,   b.component_quantity
      ,   b.component_quantity
      ,   a.BOM_Level + 1
      ,   a.Demand_Quantity
      ,   a.Produced_Ratio * a.Component_Quantity      -- Produced Quantity for the current level = Produced Quantity (level -1) * Component_Quantity (level -1)
      ,   a.Demand_Material_CD
      FROM BOM a 
      inner join #BOM_ProdVersion_Priority b  
          on a.component_cd = b.material_cd
      inner join #base_routes c
          on a.Demand_Plant_CD = c.Recipient_Plant_CD 
         and b.Plant_CD        = c.Source_Plant_CD
         and c.Material_CD     = b.Material_CD   -- Need to have material_cd to link
      where b.Material_CD != b.Component_CD
      and   b.Component_Quantity &gt; 0
      and   BOM_Level &lt; 5 -- Give the max number of levels deep we are allowed to cyncle to
  )
  
  select *
  into #BOMExploded
  from BOM
  OPTION (MAXRECURSION 20)
  </code></pre>
  
  <p>Using this method however, would require a post-process to locate when the cycling on the recursive component level started, then back trace.</p>
  
  <p>How can a CTE recursive query be stopped given a certain condition? </p>
  
  <p><code>ie. when top-level material_cd = component_cd for a deeper BOM_Level</code></p>