title: How to retrieve hierarchical data from a SQL Table?
body: <p>I have got the below stored procedure to return the list of Id, parentId and absoluteUrls which works fine:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SearchDataManager.HierarchyById] 
      @currentId AS int   
  AS
  BEGIN
   DECLARE @id INT 
   DECLARE @parentId INT
   DECLARE @absoluteUrl NVARCHAR(1000)
  
   DECLARE @Hierarchy TABLE (Id int, ParentId int, AbsoluteUrl nvarchar(1000))
  
   WHILE @currentId != 0
   BEGIN
  
       SELECT @id = Id, @parentId = ParentId, @absoluteUrl = AbsoluteUrl
       FROM dbo.[SearchDataManager.NiceUrls]
       WHERE id = @currentId
  
       INSERT INTO @Hierarchy  VALUES (@id, @parentId, @absoluteUrl)
  
      SET @currentId = @parentId
   END
  
      SELECT * FROM @Hierarchy  
  END
  </code></pre>
  
  <p>The "NiceUrls" table has Id and ParentId. parentId refers to a record in the same table.</p>
  
  <p>it returns like:   </p>
  
  <pre><code> ----------------------------------
      Id  | ParentId | AbsoluteUrl
      ----------------------------------
      294 | 5        | url1
      5   | 2        | url2
      2   | 0        | url3
  </code></pre>
  
  <p>The above code works fine using a WHILE loop and defining a Table variable but I'm just wondering is there any better way to retrieve hierarchy data from a table?</p>
  
  <p>The problem with the above code is maintainability. If I'd need to return 1 more column of the NiceUrls table then I'd have to define a new variable, add the column to the inline table, etc.</p>
  
  <p>Is there any better way to rewrite the sp?</p>
  
  <p>Thanks,</p>
  
  <p>What's the</p>