我们经常使用树型菜单，把树型菜单弄成动态的，经常要查询某个结点下的所有的子结点，或者往某个结点下插入新的结点，可以使用以下代码的存储过程来实现查询某一结点下的所有结点，下面提供了测试代码，
create table UDS_TreeMenu
(
nodeID int not null,
childID int not null,
nodeText varchar(50) not null,
nodeURL varchar(1024)
)
/*
create FUNCTION TreeFun2(@id int)
RETURNS @t Table(id int,child_id int,name char(20))
AS
BEGIN
insert into @t(id,child_id,name) select id,child_id,name from Test_connect where id = @id
while(@@rowcount > 0)
begin
insert into @t(id,child_id,name)
select B.id, B.child_id, B.name from @t A Inner Join Test_connect B on B.id=A.child_id where B.child_id
Not In(select Distinct child_id from @t)
end
return
END
*/
go
create procedure GetTree
@ID int
as
declare @treeTable table
(
nodeID int,
childID int,
nodeText varchar(50),
nodeURL varchar(50)
)
begin
insert into @treeTable(nodeID,childID,nodeText,nodeURL)
select nodeID,childID,nodeText,nodeURL from UDS_TreeMenu where nodeID=@ID
while(@@rowcount>0)
begin
insert into @treeTable(nodeID,childID,nodeText,nodeURL)
select TM.nodeID,TM.childID,TM.nodeText,TM.nodeUrL from @treeTable TT inner join UDS_TreeMenu TM on
TM.nodeID=TT.childID where TM.childID
not in (select Distinct childID from @treeTable)
end
select * from @treeTable
end
delete UDS_TreeMenu where nodeID<>''
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,0,'我的桌面','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'公告','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'新闻','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'投票','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'通信薄','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'工作日志','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'电子邮件','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'个人文档柜','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,0,'考勤管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'员工管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'部门管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'单位管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'考勤查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,0,'工作管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'个人工作计划','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'单位工作计划','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'项目管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'报表中心','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'工作总结','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,0,'信息交流','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'聊天室','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'在线短信','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'意见反馈','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'论坛','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,0,'人事管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,6,'档案信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,7,'部门管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,8,'奖惩管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,9,'培训管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(6,6,'档案管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(6,6,'档案查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'部门信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'部门查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'员工查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(8,8,'奖惩记录','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(8,8,'奖惩维护','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(9,9,'培训记录','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(9,9,'培训维护','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,0,'资源管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,11,'图书资源管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,12,'办公用品管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书类别管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(12,12,'办公信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(12,12,'办公用品登记','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,0,'车辆信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,13,'车辆使用申请','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,13,'车辆使用查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,14,'车辆管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'使用情况','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'使用审批','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'车辆信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'维护管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,0,'销售管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,16,'客户关系','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,17,'销售管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,18,'供应商','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,19,'统计分析','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'客户信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'联系人信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'客户服务管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'综合查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'产品信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'销售合同管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'销售记录管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'综合查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(18,18,'供应商信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(18,18,'供应商联系人管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'客户统计','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'客户服务统计','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'销售统计','')
exec GetTree 1
|