insert into emp values(1,'董事长',null) insert into emp values(2,'总经理',1)insert into emp values(3,'技术部总监',2) insert into emp values(4,'人事部总监',2)insert into emp values(5,'编码组组长',3)
create function fn_query( @No int ) returns @r table ( empNo int, empName varchar(20), managerNo int ) as begin insert @r select * from emp where empNo=@No while exists (select 1 from emp where empNo not in (select empNo from @r) and managerNo in (select empNo from @r) ) insert @r select * from emp where empNo not in (select empNo from @r) and managerNo in (select empNo from @r) return end go--查询 select * from dbo.fn_query(2) go select * from dbo.fn_query(1) go
insert into emp values(2,'总经理',1)insert into emp values(3,'技术部总监',2)
insert into emp values(4,'人事部总监',2)insert into emp values(5,'编码组组长',3)
@No int
)
returns @r table (
empNo int,
empName varchar(20),
managerNo int
)
as
begin
insert @r select * from emp where empNo=@No
while exists (select 1 from emp where empNo not in (select empNo from @r)
and managerNo in (select empNo from @r)
)
insert @r select * from emp where empNo not in (select empNo from @r)
and managerNo in (select empNo from @r)
return
end
go--查询
select * from dbo.fn_query(2)
go
select * from dbo.fn_query(1)
go