declare id int;
insert into MenuTable values(0,'Menu0','/Menu/0',0);
set id = @@identity;
insert into MenuTable values (id,'Menu1','/Menu/1',0);

 1 create proc sqlGetMenuTree
 2 (
 3     @p_pid id,   --上级菜单编号
 4     @p_level int --当前菜单登录(在显示的时候可能会用到)
 5     )
 6 as
 7 begin
 8 
 9     declare @v_pid uniqueidentifier
10     declare @v_level int
11     set @v_pid=@p_pid
12     set @v_level=@p_level
13 
14     declare @v_id uniqueidentifier
15     declare @v_name varchar(100)
16     declare @v_issys bit
17     declare crsr cursor local forward_only for
18         select col_id,col_name
19         from sys_menus
20         where col_pid=@p_pid
21         order by col_order
22     open crsr
23     fetch next from crsr into @v_id,@v_name
24     while(@@fetch_status=0 )
25     begin
26             insert into #temp_menu
27             select @v_id,@v_pid,@v_name,@v_level
28             declare @c_level int
29             set @c_level=@v_level+1
30             
31             exec sqlGetMenuTree @v_id,@c_level
32             fetch next from crsr into  @v_id,@v_name
33     end 
34     close crsr
35     deallocate crsr
36 end

 1     create table #temp_menu
 2     (
 3         col_id uniqueidentifier,
 4         col_pid uniqueidentifier,
 5         col_name varchar(50),
 6         col_level int,
 7     )
 8     exec sqlGetMenuTree '0',0
 9     select  
10         col_level,
11         col_id,
12         col_name,
13         replicate('  ',col_level)+col_name as col_disname,
14         a.col_pid
15     from #temp_menu