How to retrieve hierarchical data from a SQL Table?

hierarchy, sql-server, t-sql, while-loop

Solution

with Hierarchy (Id, ParentId, AbsoluteUrl, Level)
 AS
 (
    -- anchor member
     SELECT Id,
        ParentId,
        AbsoluteUrl,
        0 AS Level   
     FROM dbo.[NiceUrls]
     WHERE id = @currentId
     UNION ALL
     -- recursive members
     SELECT su.Id,
        su.ParentId,
        su.AbsoluteUrl,
        Level + 1 AS Level   
     FROM dbo.[NiceUrls] AS su
     INNER JOIN Hierarchy ON Hierarchy.ParentId = su.Id  
 )
 SELECT * FROM Hierarchy

Problem

I have got the below stored procedure to return the list of Id, parentId and absoluteUrls which works fine: ``` 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 ``` The "NiceUrls" table has Id and ParentId. parentId refers to a record in the same table. it returns like: ``` ---------------------------------- Id | ParentId | AbsoluteUrl ---------------------------------- 294 | 5 | url1 5 | 2 | url2 2 | 0 | url3 ``` 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? 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. Is there any better way to rewrite the sp? Thanks, What's the

Original source