62,046
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION get_treename (@treeid int)
RETURNS @temp table (treename varchar(200))
AS
BEGIN
declare @treename varchar(200)
declare @parentid varchar(50)
insert into @temp select treename from pertree where treeid=@treeid
set @parentid=(select top 1 treeid from pertree where parentid=@treeid)
while(@parentid is not null)
begin
insert into @temp select treename from pertree where treeid=@parentid
set @parentid=(select top 1 treeid from pertree where parentid=@parentid)
end
return
END
--调用
select * from get_treename(0)
create table t (nodeID int ,f_ID int ,name varchar(30))
insert into t values (2,1,'泰安市交通局')
insert into t values(3,2,'新泰市交通局')
insert into t values(4,3,'新泰市运输公司')
go
alter proc prGetTree(@Nid int)
as begin
declare @t table(fid int, id int)
insert into @t select f_id ,NodeID from t where nodeid= @nid
while @@rowcount>0
begin
print 1
insert into @t
select f_ID ,nodeID
from t
join @t a on f_ID = id
where not exists(select 1 from @t where a.id =FID)
end
--if you want to return a datatable
--select * from @t
declare @str varchar(1000)
select @str = ''
select @str = @str + cast(id as varchar(10)) + ';' from @t
select @str
return
end
go
prGetTree 2
select NodeID,f_Id,Name from table1 where NodeID=3 or f_Id=3 or f_Id in(select NodeID from table1 where f_Id=3)
select NodeID,f_Id,Name from table1 where NodeID=3 or f_Id=3 or f_Id in(select NodeID from table1 where f_Id=3)