quote:

---

Originally posted by versus69

ok now since i am new to sql server and just can do basic stuff...

how do you create these CTE's. if i go to stored procedures and ask to add a new one and then use the code it says it cant because it says
SQL batch must begin with CREATE or ALTER

---

CREATE TABLE tbPeople 
(
	[ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Name] varchar(64) NOT NULL,
	[ParentId] int NULL,
	[Lineage] varchar(255) NULL
)
GO

INSERT INTO tbPeople ([Name], [ParentId])
SELECT 'Steven', NULL 
UNION ALL
SELECT 'Joe', NULL 
UNION ALL
SELECT 'Chris', 1 
UNION ALL
SELECT 'Mark', 1 
UNION ALL
SELECT 'Joseph', 4 
UNION ALL
SELECT 'Daniel', 2 

UPDATE tbPeople SET [Lineage] = '/'+LTRIM(STR([ID]))+'/'
WHERE [ParentId] IS  NULL

UPDATE c SET [Lineage] = p.Lineage+LTRIM(STR(c.[ID]))+'/'
FROM tbPeople p JOIN tbPeople c ON p.[ID] = c.[ParentId]
WHERE c.[ParentId] IS NOT NULL

CREATE PROCEDURE usp_GetPeople 
@ID int
AS
DECLARE @LINEAGE varchar(255)
SELECT @LINEAGE = [Lineage]+'%' FROM tbPeople WHERE [ID]=@ID
SELECT * FROM tbPeople WHERE [Lineage] like @LINEAGE
GO

EXEC usp_GetPeople 1

-- Prepare sample data
DECLARE @People TABLE
	(
		[ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
		[Name] varchar(64) NOT NULL,
		[ParentId] int NULL,
		[Lineage] varchar(255) NULL
	)

INSERT @People ([Name], [ParentId])
SELECT 'Steven', NULL UNION ALL
SELECT 'Joe', NULL UNION ALL
SELECT 'Chris', 1 UNION ALL
SELECT 'Mark', 1 UNION ALL
SELECT 'Joseph', 4 UNION ALL
SELECT 'Daniel', 2 

;WITH lineage(Name, ID, ParentID, lineage)
AS (SELECT p.Name,
        p.ID,
        p.ParentID,
        CONVERT(varchar(255), '\' + p.Name)
    FROM @People AS p
    WHERE p.ParentID IS NULL -- Here is where you change you serach criteria to equal an id instead
    UNION ALL
    SELECT x.Name,
        x.ID,
        x.ParentID,
        CONVERT (varchar(255), l.lineage + '\' + x.Name)
    FROM @People as x
    INNER JOIN lineage AS l ON x.parentID = l.ID
    )

-- show the output
select id, name, parentid, lineage from lineage order by id

quote:

---

Originally posted by Peso

Great! That will work for two levels

...

Peter Larsson
Helsingborg, Sweden

---