Web Listing 2: Script to Create Stored Procedure usp_AddEmp
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
  DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
  @empid   AS INT,
  @mgrid   AS INT = NULL,
  @empname AS VARCHAR(25),
  @salary  AS MONEY
AS

DECLARE
  @hid            AS HIERARCHYID,
  @mgr_hid        AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);
GO