How to get hierarchical data from SQL table?

Question

I have the stored procedure below to return a 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 

In the table "NiceUrls" there are Id and ParentId. parentId refers to a record in the same table.

it returns:

  ---------------------------------- Id | ParentId | AbsoluteUrl ---------------------------------- 294 | 5 | url1 5 | 2 | url2 2 | 0 | url3 

The above code works fine using the WHILE loop and defining the Table variable, but I'm just wondering if there is a better way to get hierarchy data from a table?

The problem with the code above is maintainability. If I need to return another column of the NiceUrls table, then I will need to define a new variable, add a column to the inline table, etc.

Is there a better way to rewrite sp?

Thanks,

what

Answer 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 

Answer 2

It looks like you want all the records from the source table to be associated with the source id.

1) Create a CTE that will give you all the identifiers (see the Three link)

2) Attach this CTE to the source table