Single Result Set from Recursive Query

i have simple locationhierarchy table id, parentlocationhierarchyid, name columns.

the proc below produces exact output want want create result set can pull datatable or whatever.

how do this? adding simple select print command creates result set each row. not want.

i imagine using sort of temp table might work, not know how impliment it.

thank help

earl

 

 

alter proc dbo.showhierarchy
(
 @root int,
 @maxlevel int
)
as
begin
 set nocount on
 declare @id int, @name varchar(30)

 set @name = (select name dbo.locationhierarchy id = @root)
 
  if @@nestlevel<@maxlevel
  begin 
   print cast(@root varchar) + ' - ' + (replicate('-', @@nestlevel * 2) + @name)
  end
 set @id = (select min(id) dbo.locationhierarchy parentlocationhierarchyid = @root)

 while @id not null
 begin
   exec dbo.showhierarchy @id, @maxlevel
   set @id = (select min(id) dbo.locationhierarchy parentlocationhierarchyid = @root , id > @id)
 end
end
go

have tried using recursive cte need? , wrap recursive cte either stored procedure or function?

  ;with cterecursive  as (  	select	id,  		name,  		0 as nestlevel  	from	dbo.locationhierarchy  	where	id = @root    	union all    	select		lh.id,  			lh.name,  			r.nestlevel + 1 as nestlevel  	from		dbo.locationhierarchy as lh  	inner join	cterecursive as r on r.id = lh.parentlocationhierarchyid  )  select	cast(id as varchar(12)) + '-' + replicate('--', nestlevel) + name  from	cterecursive    

SQL Server  >  Transact-SQL