How to retrieve hierarchical data from a SQL Table?

Question

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

Solution 1

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

Solution 2

Looks like you want all the records from the source table that are related to the original id.

1) Create a CTE that gives you all the ids (see the link Triple noted)

2) Join this CTE to the original table

Comments

[Triple Gilaman]

A recursive CTE should do what you need - msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

[Marco Serralheiro]

Nice! And if we need all the children of the @currentId element (instead of all the parents), just invert the condition in the JOIN: "INNER JOIN Hierarchy ON Hierarchy.Id = su.ParentId".

[Marco Serralheiro]

The query is way faster this way!