Databases often store hierarchical information. The following data, for example, is a hierarchical representation of regions of the world:
Parent |
Child |
------------- |
------------- |
World |
Europe |
World |
North America |
Europe |
France |
France |
Paris |
North America |
United States |
North America |
Canada |
United States |
New York |
United States |
Washington |
New York |
New York City |
Washington |
Redmond |
This representation does not clearly show the structure implied by the data. The following example is easier to interpret:
World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris
The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete. When processing is complete for a particular item, it is removed from the stack. New items are added to the stack as they are identified. (Note that this example is for illustration only and it does not come from the pubs database.)
create proc expand (@current char(20)) as
set nocount on
declare @level int, @line char(20)
create table #stack (item char(20), level int)
insert into #stack values (@current, 1)
select @level = 1
while @level > 0
begin
if exists (select * from #stack where level = @level)
begin
select @current = item
from #stack
where level = @level
select @line = space(@level - 1) @current
print @line
delete from #stack
where level = @level
and item = @current
insert #stack
select child, @level 1
from hierarchy
where parent = @current
if @@rowcount > 0
select @level = @level 1
end
else
select @level = @level - 1
end
In this example, the input parameter (@current) is used to indicate the place in the hierarchy to start. It also keeps track of the current item in the main loop.
The two local variables used are @level, which keeps track of the current level in the hierarchy, and @line, which is a work area used to construct the indented line.
The SET NOCOUNT ON statement avoids cluttering up the output with ROWCOUNT messages from each SELECT.
The temporary table, #stack, is created and primed with the item identifier of the starting point in the hierarchy, and @level is set to match. The level column in #stack allows the same item to appear at multiple levels in the database. Although this situation does not apply to the geographic data in the example, it can apply in other examples.
In this example, when @level is greater than 0, the procedure follows several steps:
Note With a conventional programming language, you would have to find each child item and add it to the stack individually. With Transact-SQL, you can find all child items and add them with a single statement, avoiding another nested loop.
This is the only place where the hierarchy table (#stack) is used.