字段名?字段類型?是否為空?主外鍵?備注Nav_Id?int??主?主鍵Nav_Parent ?Int ???父節點Nav_Title?Char(20)???標題Nav_Url?Char(100)???連接Nav_Statae?Int ???狀態（默認0正常）Nav_ Reserve?Char(100)???備用
?
如何數據庫遞歸添加數據，2級、3級菜單，怎么添加，和查詢，謝謝！幫幫忙！謝謝了！
4 回答
海綿寶寶撒
TA貢獻1809條經驗 獲得超8個贊
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
- 4 回答
- 0 關注
- 463 瀏覽
添加回答
舉報
0/150
提交
取消