--typeCode根目录代码 Menucode 子目录代码
create function f_getC(@id int)
returns @re table(id int,level int,sort varchar(100))
as
begin
declare @l int
set @l=0
insert @re select Menucode,@l,right('00'+ltrim(Menucode),2)
from tb_Menu where typeCode=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re
select a.Menucode,@l,b.sort+right('00'+ltrim(a.Menucode),2)
from tb_Menu as a,@re as b
where b.id=a.typeCode and b.level=@l-1
end
update @re
set level = level
return
end
go
select * from tb_Menu a,f_getC(0) b where
a.Menucode=b.id order by sort