Microsoft KB Archive/275740

{|
 * width="100%"|

INF: How to Show Expanding Hierarchies By Using SQL Server

 * }

Q275740

-

The information in this article applies to:


 * Microsoft SQL Server versions 6.5, 7.0
 * Microsoft SQL Server 2000 (all editions)

-

SUMMARY
Databases often store hierarchical information. For more details about hierarchical information, refer to the &quot;Expanding Hierarchies&quot; topic in Microsoft SQL Server Books Online. The &quot;Expanding Hierarchies&quot; topic has a detailed Transact-SQL procedure, which expands an encoded hierarchy to any arbitrary depth.

However, the SQL Server Books Online procedure incorrectly declares @level, which is a reserved word. The &quot;More Information&quot; of this article has a corrected version of the procedure that substitutes @lvl in place of the reserved word. The procedure has also been enhanced to include the CREATE TABLE statement, CREATE INDEX samples, and INSERT statements that are needed to demonstrate the example.

MORE INFORMATION
Here is the corrected code sample:

CREATE TABLE hierarchy (parent VARCHAR(20) NOT NULL, child VARCHAR(20), CONSTRAINT UIX_parentchild UNIQUE NONCLUSTERED (parent,child) )

CREATE CLUSTERED INDEX CIX_parent ON hierarchy(parent)

GO INSERT hierarchy VALUES('World','Europe') INSERT hierarchy VALUES('World','North America') INSERT hierarchy VALUES('Europe','France') INSERT hierarchy VALUES('France','Paris') INSERT hierarchy VALUES('North America','United States') INSERT hierarchy VALUES('North America','Canada') INSERT hierarchy VALUES('United States','New York') INSERT hierarchy VALUES('United States','Washington') INSERT hierarchy VALUES('New York','New York City') INSERT hierarchy VALUES('Washington','Redmond') GO

CREATE PROCEDURE expand (@current char(20)) as

SET NOCOUNT ON DECLARE @lvl int, @line char(20)

CREATE TABLE #stack (item char(20), lvl int) INSERT INTO #stack VALUES (@current, 1)

SELECT @lvl = 1 WHILE @lvl > 0 BEGIN

IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl) BEGIN SELECT @current = item FROM #stack WHERE lvl = @lvl

SELECT @line = space(@lvl - 1) + @current PRINT @line

DELETE FROM #stack WHERE lvl = @lvl AND item = @current

INSERT #stack SELECT child, @lvl + 1 FROM hierarchy WHERE parent = @current

IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END -- WHILE

GO EXEC expand 'World' Additional query words: Expanding Hierarchies

Keywords : kbSQLServ650 kbSQLServ700 kbDSupport kbCodeSnippet kbSQLServ2000

Issue type : kbinfo

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ700 kbSQLServ650 kbSQLServ2000Search kbSQLServ2000