树形

CREATE PROCEDURE pro_Menu  AS
select a.*
from tablename a join f_id() b on a.id=b.id
order by b.sid
GO
create function f_id()
returns @re table(id int,sid varchar(8000))
as
begin
 --为了数字排序正常,需要统一编码宽度
 declare @idlen int,@idheader varchar(20)
 select @idlen=max(len(id))
  ,@idheader=space(@idlen)
 from tablename

 declare @l int
 set @l=0
 insert into @re select id ,right(@idheader+cast(id as varchar),@idlen)
 from tbl_menu where I_Depth=@l
 while @@rowcount>0
 begin
  set @l=@l+1
  insert into @re select a.id ,b.sid+','+right(@idheader+cast(a.id as varchar),@idlen)
  from tablename a join @re b on a.pid =b.id and a.I_Depth=@l
 end
 return
end

加一个深度I_Depth