SQL tree data struct(4): AdjList2MatPath (for printing tree struct)

if object_id('proc_print_tree') is not null
    drop procedure proc_print_tree
go

create procedure proc_print_tree @raw_data_sql nvarchar(1000), @debug bit = 0
with encryption
as
    if object_id('tempdb..#adjacency_list') is not null
        drop table #adjacency_list
    create table #adjacency_list(code nvarchar(30) collate database_default, parent nvarchar(30) collate database_default)
    if @debug = 1 print @raw_data_sql
    insert #adjacency_list exec(@raw_data_sql)

    if @debug = 1 select [data]='', * from #adjacency_list

    if object_id('tempdb..#materialized_path') is not null
        drop table #materialized_path   
    create table #materialized_path( code nvarchar(30) collate database_default, parent nvarchar(30) collate database_default,
        height int, path nvarchar(3900) collate database_default)

    declare @level int
    select @level = 0
    insert #materialized_path select code, null, 0, code from #adjacency_list where parent is null
    while @@rowcount > 0
    begin
        select @level = @level + 1
        insert into #materialized_path
        select b.code, b.parent, @level, a.path + b.code
        from #materialized_path a inner join #adjacency_list b on a.code = b.parent -- can not use left join!!!
        where height = @level-1 and b.parent <> b.code
    end

    if @debug = 1 select [tree]='', * from #materialized_path order by path

    if not exists(select * from #materialized_path)
    begin
        print 'no data exists in #materialized_path!'
        return
    end
   
    declare @h int, @i int, @sql nvarchar(1000)
    select @h = (select max(height) from #materialized_path), @i = 0, @sql = '0'
    while @i <= @h
        select @sql = @sql + ',case height when ' + cast(@i as varchar) +
            ' then code else '''' end as hierarchy_level_' + cast(@i as varchar) + char(13),
            @i = @i + 1

    set @sql = 'select '  + char(13) + substring(@sql, 3, 1000) + char(13) + ' from #materialized_path order by path'
    if @debug = 1 print @sql
    exec(@sql)

    drop table #adjacency_list, #materialized_path
go

/*
print isnull(object_id('tempdb..#adjacency_list'), -1)
print isnull(object_id('tempdb..#temp'), -1)

exec proc_print_tree
'select distinct object_name(rkeyid), NULL
from sysreferences a
where not exists (select * from sysreferences b where a.rkeyid = b.fkeyid)
union all
select object_name(fkeyid), object_name(rkeyid) from sysreferences
'

exec proc_print_tree
'select ''APDocA'', NULL
union all
select object_name(fkeyid), object_name(rkeyid) from sysreferences
', 1
*/