数据库递归添加数据

Question

字段名 字段类型 是否为空 主外键 备注Nav_Id int  主 主键Nav_Parent  Int    父节点Nav_Title Char(20)   标题Nav_Url Char(100)   连接Nav_Statae Int    状态（默认0正常）Nav_ Reserve Char(100)   备用   如何数据库递归添加数据，2级、3级菜单，怎么添加，和查询，谢谢！帮帮忙！谢谢了！

Answer 1

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