LISTING 4: funcExpandHierarchy as a User-Defined Function

CREATE FUNCTION funcExpandHierarchy
(@Start INT )
RETURNS @Holder TABLE (SerialNo int, No int, Head varchar(300) )
As
BEGIN

DECLARE @nLevel INT, @Current INT
Declare @Stack TABLE ( nLevel INT, KeyNo INT)
Declare @Hierarchy TABLE (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
RETURN
END