Given a string path, find the leaf node

Question

I am using this table and data where all children of a parent node are uniquely named.

CREATE TABLE [dbo].[Resources](
    [Node] [hierarchyid] NOT NULL,
    [Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/', N'Resource Root')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/1/', N'Applications')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/1/1/', N'Accounting')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/1/1/1/', N'Dashboard')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/1/2/', N'Asset Management')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/1/2/1/', N'Dashboard')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/2/', N'Assets')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/2/1/', N'X100022')
INSERT [dbo].[Resources] ([Node], [Name]) VALUES (N'/2/2/', N'X100017')
GO

The Resources table contains a hierarchy represented by this path 'Applications/Accounting/Dashboard'. The SP below gets the leaf node named 'Dashboard'.

I would like to know if it can be done without a cursor. Or is there a more performant way to do this?

CREATE OR ALTER PROCEDURE [dbo].[GetLeafNode]
  @Path NVARCHAR(500)
AS
  DECLARE @Name NVARCHAR(50)
  DECLARE @ParentNode HIERARCHYID = HIERARCHYID::GetRoot()
  DECLARE @Level INT

  DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM string_split(@Path,'/')

  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @Name, @Level

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @ParentNode = Node FROM dbo.Resources WHERE Node.GetAncestor(1) = @ParentNode AND Name = @Name 
    FETCH NEXT FROM db_cursor INTO @Name, @Level
  END

  CLOSE db_cursor
  DEALLOCATE db_cursor

  IF EXISTS(SELECT * FROM dbo.Resources WHERE Node = @ParentNode AND Node.GetLevel() = @Level)
    SELECT @ParentNode

exec GetLeafNode 'Applications/Accounting/Dashboard'

Returns 0x5AD6 which is correct.

Answer 1

SELECT 
A.HieracrchyId
,A.HierarchyId.ToString()
FROM [dbo].[Resources] AS A 
LEFT OUTER JOIN [dbo].[Resources] AS B
ON A.HierarchyId = B.HierarchyId.GetAncestor(1)
WHERE B.HierarchyId IS NULL

There's some good (but verbose) instructions around using hierarchyid which includes descriptions of methods like GetAncestor() and GetChild() etc.

There's other methods of representing a heirarchy in sql that you could look into like closure tables which can be more performant under certain conditions if you're not worried about the extra space taken up by storing the edges of the graph. It also allows you to detect circular references which can be helpful