LISTING 3: Revised ExpandHierarchy Stored Procedure

CREATE PROCEDURE ExpandHierarchy
@Start INT -- Where to Start
AS
/*Source Table
CREATE TABLE [aclist] (
	[no] 	int ,
	control int,
	[head] 	varchar(50),
) 
*/

SET NOCOUNT ON
DECLARE @nLevel INT, @Current INT
CREATE TABLE #Stack( nLevel INT, KeyNo INT)
CREATE TABLE #Hierarchy(SerialNo INT identity, nLevel INT ,KeyNo INT)

--

IF @start IS NULL
 SELECT TOP 1  @nLevel=1,@current=No FROM AcList WHERE  control IS NULL ELSE
 SELECT TOP 1 @nLevel=1,@current=No fROM AcList WHERE No=@Start 

INSERT INTO #stack VALUES (@nLevel,@current)

WHILE (@nLevel >0)
 BEGIN
	IF EXISTS (SELECT * FROM #stack WHERE nLevel =@nLevel) 
	 -- Any thing in current Level

	 BEGIN
		-- Pop One Record
		SELECT TOP  1 @Current =KeyNo
FROM #Stack WHERE @nLevel=nLevel
		DELETE FROM #Stack WHERE KeyNo=@Current AND @nLevel=nLevel
		--Pop One Record

		-- Insert the record into hierarchy Table
		INSERT INTO #Hierarchy( nLevel,KeyNo)
VALUES (@nLevel,@Current)

		--Insert Leaf Records (One with no Children) Directly in 
--#hierarchy Table

		INSERT INTO #Hierarchy( nLevel,KeyNo) 
			SELECT  @nLevel+1, a.No 
FROM Aclist a LEFT OUTER JOIN AcList b
					ON b.Control=a.No
  	WHERE a.control=@Current AND b.No IS NULL

--Insert non-Leaf (One with have some children) in #Stack 
--Table
		INSERT INTO #stack
			SELECT @nLevel+1, a.No 
FROM Aclist a INNER JOIN AcList b
					ON b.Control=a.No  
WHERE a.control=@Current

		--if any Leaf Found increment the @nLevel
		IF @@RowCount >0
			SELECT @nLevel=@nLevel +1
	  END
 	ELSE -- if (exists (Select  * from #stack where  nLevel =@nLevel)
		-- Nothing in current Level Go to Previous Level
		SELECT @nLevel= @nLevel -1
 End

INSERT INTO #HOlder
	SELECT a.SerialNo,No,Space((a.nLevel -1) * 3)+b.Head
		FROM #Hierarchy a, AcList b
		WHERE b.No =a.KeyNo
		ORDER BY a.SerialNo
SET NOCOUNT OFF