title: How to loop through a result set and repeat the same thing until last level
body: <p>I am trying to get the last layer from a product category. The twist is, the category got sub category and the last layer only have parentId of it's intimidate sub category.So I am trying to get the subcategory and then use that subcategory id to do the same thing again until it reaches to the last category. The only way to identify the last category is the linetype. So I was thinking to use while loop, but I am all confused.</p>
  
  <p>For example</p>
  
  <pre><code>Declare @x Varchar(200)
  Set @x = 'H506563'
  
  Select stockCode
  FROM stock
  Where ParentStockCode = @x 
  </code></pre>
  
  <p>It returns set of stockCode which will be the ParentStockCode in the next iteration and then those each will again give set of stockCodes. So this process will continue until I reach into the last level where the lineType will be S.</p>
  
  <p>Is there any way to do that. Any suggestions will be helpful. Thanks
  Hope this will help to understand better</p>
  
  <p><a href="https://i.stack.imgur.com/p4ZxF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/p4ZxF.png" alt="enter image description here"></a></p>
  
  <p>The Last level which has the Part numbers - </p>
  
  <p><a href="https://i.stack.imgur.com/1dCfC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1dCfC.png" alt="enter image description here"></a></p>