SQL Server HierarchyId: Moving Subtree Issue

Question

我创建了一个如下的存储过程:

CREATE PROCEDURE [SPNAME] 
    @UserCode INT, 
    @ReportsTo INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @HierarchyId HIERARCHYID
    DECLARE @NewParentHierarchyId HIERARCHYID
    DECLARE @OldParentHierarchyId HIERARCHYID
    SELECT  @HierarchyId = [Hierarchy]
    FROM    [aspnet_Users]
    WHERE   [UserCode] = @UserCode
    SELECT  @OldParentHierarchyId = [Hierarchy].GetAncestor(1)
    FROM    [aspnet_Users]
    WHERE   [UserCode] = @UserCode
    PRINT N'Old Root: ' + @OldParentHierarchyId.ToString()
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        SELECT  @NewParentHierarchyId = [Hierarchy]
        FROM    [aspnet_Users]
        WHERE   [UserCode] = @ReportsTo
        PRINT N'New Root: ' + @NewParentHierarchyId.ToString()
        PRINT N'Old HierarchyId: ' + @HierarchyId.ToString()
        SELECT  @HierarchyId = @NewParentHierarchyId.GetDescendant(MAX([Hierarchy]), NULL)
        FROM    [aspnet_Users]
        WHERE   [Hierarchy].GetAncestor(1) = @NewParentHierarchyId
        PRINT N'New HierarchyId: ' + @HierarchyId.ToString()
        UPDATE  [aspnet_Users]
        SET     [Hierarchy] = @HierarchyId.GetReparentedValue(@OldParentHierarchyId, @NewParentHierarchyId)
        WHERE   [Hierarchy].IsDescendantOf(@OldParentHierarchyId) = 1
    COMMIT TRANSACTION
END

我的分层数据如下:

  imirza10 (UserCode 1)
  -adil    (UserCode 3)
  --arif   (UserCode 5)
  ---saqib (UserCode 6)
  -farhan  (UserCode 4)

上面的存储过程是为了将arif从adil移到farhan。

当我用语句执行sp时:

[aspnet_Users_ChangeHierarchy] 5, 4

它在SSMS的message选项卡上显示如下:

Old Root: /1/1/
New Root: /1/2/
Old HierarchyId: /1/1/1/
New HierarchyId: /1/2/1/

Msg 6522，级别16，状态2，过程aspnet_Users_ChangeHierarchy，第48行
.NET Framework在执行用户定义例程或聚合"hierarchyid"时发生错误:
microsoft . sqlserver . types . hierarchyidexeption: 24009: SqlHierarchyId. sqlgettreparentedvalue失败，因为' olroot '不是'this'的祖先节点。"oldRoot"是"/1/1/"one_answers"这个"/1/2/1"。
Microsoft.SqlServer.Types.HierarchyIdException:
在Microsoft.SqlServer.Types.SqlHierarchyId。gettreparentedvalue (SqlHierarchyId olroot, SqlHierarchyId newRoot)

需要帮助

Answer 1

未经任何进一步测试，我在您的查询中看到以下问题:

1. 错误信息告诉你的是你不能将@HierarchyId从@OldParentHierarchyId代表到@NewParentHierarchyId，因为@HierarchyId不是@OldParentHierarchyId的祖先(你已经计算出它是新的路径)。
2. 您使用@HierarchyId.GetReparentedValue来计算新位置而不是实际值。这将给所有行相同的结果。
3. 您需要从更新中排除@OldParentHierarchyId。

UPDATE查询应该是这样的:

UPDATE [aspnet_Users]
SET    [Hierarchy] = [Hierarchy].GetReparentedValue(@OldParentHierarchyId, @NewParentHierarchyId)
WHERE  [Hierarchy].IsDescendantOf(@OldParentHierarchyId) = 1 AND [Hierarchy] <> @OldParentHierarchyId

我知道我在这个问题上晚了，但它可能会帮助别人。