Hi all,

From Code Project, I copied the following Version 2008 Code of creating Table and Stored Procedure for doing HierarchyID:

-- CodeProjectHierarchyID.sql Version2008
-- 25 August 2015  9:01 AM
USE scDB;
GO
CREATE TABLE dbo.Employees
(
  empid   INT NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_depth_first
  ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first
  ON dbo.Employees(lvl, hid);
CREATE UNIQUE INDEX idx_empid
  ON dbo.Employees(empid);

CREATE PROC ado.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);
-- Subtree
SELECT C.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND P.hid.IsDescendant(C.hid) = 1;
-- Path
SELECT P.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND P.hid.IsDescendant(C.hid) = 1;
-- Presentation/sorting
SELECT
  REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;

I executed the code in my Microsoft SQL Server 2012 Management Studio. It did not work and the error Message is "Msg 111, Level 15, State 1, Procedure ado.usp_AddEmp, Line 23 'CREATE/ALTER Procedure' must be the first statement in a query batch."   I am very new in learning HierarchyID by doing the TABLE and STORED PRECEDURE and I have no ideas how to fix it.  Please kindly help, advise how to fix this problem and respond.

Thanks in advance,

Scott Chang

P. S. I don't know how to copy the code and the error messages together and post them in my post.  Please tell me how to do this kind of thing and what tool I should to use to get it posted in my thread.

• Edited by Scott H Chang 16 hours 11 minutes ago

>Line 23 'CREATE/ALTER Procedure' must be the first statement in a query batch."

This means that the statement at line 23 cannot appear at the end of another batch.  It must be the first statement in the batch. 

A batch is one or more statements, and a line containing only 'GO' is the batch delimiter.

David

In addition to David, besides adding a "GO" before the "CREATE PROC", more corrections to the code.

/*if you got error like ""ado" either does not exist or.."
 change the ado to dbo, not sure if that is a typo*/

CREATE PROC ado.usp_AddEmp( 

--IsDescendant is not recoginzed
--use IsDescendantOf

-- Subtree
SELECT C.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND P.hid.IsDescendantOf(C.hid) = 1;
-- Path
SELECT P.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND P.hid.IsDescendantOf(C.hid) = 1;

If you have any question, feel free to let me know.