Search code examples
sqlssmshierarchyid

SQL HierarchyId Sometimes Producing Wrong Path

I have a list of products, sorted using a HierarchyId, but for some of the items the full path up the hierarchy is producing the wrong result. Only for a few... Ill demonstrate below:

SELECT
    ProductId
    ,Name
    ,FullName
    ,Hierarchy
    ,Hierarchy.ToString() as hierarchyString
    ,editor.ufn_Product_GetFullName(ProductId) as ufnGetFullName
    ,Hierarchy.GetLevel() as [level]
FROM Editor.Product 
WHERE ProductId = 378
    OR Hierarchy.ToString() like '/16/1/1/%'
    OR Hierarchy.ToString() = '/16/1/'
    OR Hierarchy.ToString() = '/16/'
    OR Hierarchy.ToString() = '/'
ORDER BY Hierarchy.ToString()

Using that query i obtain the result:

enter image description here Most of the paths it returns from the procedure are fine, producing

'Exterior > Render > Colour > White'

however four of them produce the last two terms swapped like

'Exterior > Render > White > Colour'

I can't for the life of me, figure out why the terms are swapped and why on only some. The hierarchy structure looks correct and the levels are all correct on the child items. I need your help...

Below is the function used to produce the path from the product hierarchy, though I don't understand how it could be the problem.

ALTER FUNCTION [Editor].[ufn_Product_GetFullName] ( @ID INT )
RETURNS VARCHAR(8000)
AS BEGIN
    -- Create and insert names into temp table
    DECLARE @NamesTable TABLE (Name varchar(8000));

    INSERT @NamesTable (Name) 
        SELECT t1.Name
        FROM (  SELECT ProductId, Hierarchy, Name
                FROM Product WITH(NOLOCK)
                WHERE (ProductId = @ID)) AS t2
            CROSS JOIN Product AS t1

        WHERE (t1.Hierarchy = t2.Hierarchy) 
        OR (t1.Hierarchy <> '/') 
        AND (t2.Hierarchy.IsDescendantOf(t1.Hierarchy) = 1)

        ORDER BY t1.Hierarchy;

    -- Define name string
    DECLARE @Name VARCHAR(8000);

    -- Coalesce names from temp table into one long string
    SELECT @Name = COALESCE(@Name + ' > ', '') + Name
    FROM @NamesTable

    -- Return full string
    RETURN @Name
END
Solution

  • Without ORDER BY clause you cannot be sure of rows order. Add column Level to @NamesTable table. Add ORDER BY clause to SELECT where you are concating strings. This should help.

    ALTER FUNCTION [Editor].[ufn_Product_GetFullName] ( @ID INT )
RETURNS VARCHAR(8000)
AS BEGIN
    -- Create and insert names into temp table
    DECLARE @NamesTable TABLE (Name varchar(8000), Level int);

    INSERT @NamesTable (Name, Level) 
        SELECT t1.Name, t1.Hierarchy.getLevel()
        FROM (  SELECT ProductId, Hierarchy, Name
                FROM Product WITH(NOLOCK)
                WHERE (ProductId = @ID)) AS t2
            CROSS JOIN Product AS t1

        WHERE (t1.Hierarchy = t2.Hierarchy) 
        OR (t1.Hierarchy <> '/') 
        AND (t2.Hierarchy.IsDescendantOf(t1.Hierarchy) = 1)

        ORDER BY t1.Hierarchy;

    -- Define name string
    DECLARE @Name VARCHAR(8000);

    -- Coalesce names from temp table into one long string
    SELECT @Name = COALESCE(@Name + ' > ', '') + Name
    FROM @NamesTable
    ORDER BY Level

    -- Return full string
    RETURN @Name
END