CREATE PROCEDURE dbo.ads_Areas_Insert 
	@areaParentID int,
	@areaName varchar(50),
	@result varchar(255) OUTPUT
AS
	SET NOCOUNT ON
	
	IF (@areaParentID > 0)
		IF EXISTS(SELECT areaID FROM ads_Areas WHERE areaID = @areaParentID)
			BEGIN
				DECLARE @PL varchar(255), @POS int, @LIN varchar(255), @LVL int
				
				SET @PL = (SELECT areaLineage FROM ads_Areas WHERE areaID = @areaParentID)
				SET @POS = (SELECT COUNT(areaID) FROM ads_Areas WHERE areaParentID = @areaParentID AND areaName < @areaName)
				SET @LIN = @PL + SPACE(1) + REPLICATE('0',(4 - LEN(CAST(@POS AS varchar)))) + CAST(@POS AS varchar)
				SET @LVL = (SELECT (areaLevel + 1) FROM ads_Areas WHERE areaID = @areaParentID)
				
				INSERT INTO ads_Areas (
					areaName, areaParentID, areaLevel, areaLineage
				) VALUES (
					@areaName, @areaParentID, @LVL, @LIN
				)
				
				DECLARE @SIB int
				DECLARE C1 CURSOR FOR SELECT areaID FROM ads_Areas WHERE ((areaParentID = @areaParentID) AND (areaName > @areaName)) ORDER BY areaName
				OPEN C1
				FETCH NEXT FROM C1 INTO @SIB
				WHILE @@FETCH_STATUS = 0
					BEGIN
						SET @POS = (@POS + 1)
						SET @LIN = @PL + SPACE(1) + REPLICATE('0',(4 - LEN(CAST(@POS AS varchar)))) + CAST(@POS AS varchar)
						
						UPDATE ads_Areas SET areaLineage = @LIN WHERE areaID = @SIB
						
						FETCH NEXT FROM C1 INTO @SIB
					END
				CLOSE C1
				DEALLOCATE C1
				
				SET @result = 'OK'
			END
		ELSE
			SET @result = 'The parent selected does not exist.'
	ELSE
		IF EXISTS(SELECT areaID FROM ads_Areas WHERE areaLevel = 0)
			SET @result = 'You cannot add a new root area.'
		ELSE
			BEGIN
				INSERT INTO ads_Areas (
					areaName, areaParentID, areaLevel, areaLineage
				) VALUES (
					@areaName, 0, 0, '0000'
				)
				
				SET @result = 'OK'
			END

quote:

---

Originally posted by Seventhnight

http://www.thesitedoctor.co.uk/SeventhNight/TreeStructs/
Part 3 talks about managing a paths table...

Let me know if you have any specific questions.

---