mickyT, Thanks for your post. I see that your solution gives me what i need (not what i asked for, but perhaps better). How do a change it run against my menu table in the database? When i change your query to this:
with cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + m.menuid sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar
I get this error:
Lookup Error - SQL Server Database Error: Types don't match between the anchor and the recursive part in column "sortPathChar" of recursive query "cte".