TSQL reparent an entire branch with hierarchyid

Reparenting a Tree branch is a PITA using the HierarchyID functions. This reparents a branch by reformating the string representation of the HierarchyID. Supports moving the branch to the root node.

 I use an ID as the primary key so the proc expects the ID's of the node records

DECLARE 
	@NodeID INT,
	@NewParentID int
	
SET @NodeID = 1074
SET @NewParentID  = 1073 

DECLARE
	@OldParent VARCHAR(100),
	@NewParent VARCHAR(100),
	@NodeKey	VARCHAR(100)

--Get the existing parent node key to string
SELECT 
	@OldParent = NodeKey.GetAncestor(1).ToString(),
	@NodeKey = NodeKey.ToString()
FROM ReportSetNode
WHERE NodeID = @NodeID

--Get the new parent to string, removing the 
SELECT @NewParent = Nodekey.ToString() FROM ReportSetNode WHERE NodeID = @NewParentID ;

--deal with the root node where NewParent is NULL
IF @NewParent IS NULL SET @NewParent = '/'

--------------Test select ----------------------
--SELECT NodeKey.ToString(),
--	@NewParent + RIGHT(Nodekey.ToString(),LEN(NodeKey.ToString()) - LEN(@OldParent)) Newkey
--FROM vwReportSetNode 
--WHERE NodeKey.IsDescendantOf(@NodeKey) = 1

--Replace the old parent with the new parent string
UPDATE vwReportSetNode SET
	NodeKey = @NewParent + RIGHT(Nodekey.ToString(),LEN(NodeKey.ToString()) - LEN(@OldParent))
WHERE NodeKey.IsDescendantOf(@NodeKey) = 1