Listing 2: Script to Create the usp_Reparent Procedure
IF OBJECT_ID('dbo.usp_Reparent') IS NOT NULL
  DROP PROC dbo.usp_Reparent;
GO
CREATE PROC dbo.usp_Reparent
  @empid    AS INT,
  @new_mgrid AS INT
AS

DECLARE
  @old_root AS HIERARCHYID,
  @new_root AS HIERARCHYID,
  @new_mgr_hid AS HIERARCHYID;

BEGIN TRAN

  SET @new_mgr_hid = (SELECT hid FROM dbo.Employees WITH (UPDLOCK)
                      WHERE empid = @new_mgrid);
  SET @old_root = (SELECT hid FROM dbo.Employees
                   WHERE empid = @empid);

  -- First, get a new hid for employee that moves
  SET @new_root = @new_mgr_hid.GetDescendant
    ((SELECT MAX(hid)
      FROM dbo.Employees
      WHERE hid.GetAncestor(1) = @new_mgr_hid),
     NULL);

  -- Next, reparent all descendants of employee that moves
  UPDATE dbo.Employees
    SET hid = hid.GetReparentedValue(@old_root, @new_root)
  WHERE hid.IsDescendantOf(@old_root) = 1;

COMMIT TRAN
GO