id name child_id --@id 为参数 --返回table declare @t table ( id int, name varchar(20), child_id int ) insert into @t(id,name,child_id) select id,name,child_id from tablename where id = @idwhile(@@rowcount > 0) begin insert into @t(id,name,child_id) select id,name,child_id from tablename from tablename where id in (select child_id from @t) end return @t
Create table tb(id int,name varchar(10),child_id int) insert into tb Select 1, 'a', 2 Union all Select 1, 'b', 3 Union all Select 2, 'c', 4 Union all Select 5, 'd', 6 Union all Select 6, 'e', 7 Union all Select 7, 'f', 8 Union all Select 2, 'g', 6 Union all Select 3, 'h', 2 -------------------------------------------- select * from tb Create procedure kpi @id int as Declare @i int Declare @name varchar(10) Declare @child_id int Declare kk cursor local for Select id,name,child_id From tb Where id=@id open kk fetch next from kk into @i,@name,@child_id while @@fetch_status=0 begin select @i,@name,@child_id exec kpi @child_id fetch next from kk into @i,@name,@child_id end close kk deallocate kk -------------执行过程--------- exec kpi 1
搞定了 : 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 = @idwhile(@@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)endreturn END
--@id 为参数
--返回table
declare @t table
(
id int,
name varchar(20),
child_id int
)
insert into @t(id,name,child_id)
select id,name,child_id
from tablename where id = @idwhile(@@rowcount > 0)
begin
insert into @t(id,name,child_id)
select id,name,child_id from tablename
from tablename
where id in (select child_id from @t)
end
return @t
insert into tb
Select 1, 'a', 2 Union all
Select 1, 'b', 3 Union all
Select 2, 'c', 4 Union all
Select 5, 'd', 6 Union all
Select 6, 'e', 7 Union all
Select 7, 'f', 8 Union all
Select 2, 'g', 6 Union all
Select 3, 'h', 2
--------------------------------------------
select * from tb Create procedure kpi
@id int
as
Declare @i int
Declare @name varchar(10)
Declare @child_id int
Declare kk cursor local for
Select id,name,child_id From tb Where id=@id
open kk
fetch next from kk into @i,@name,@child_id
while @@fetch_status=0
begin
select @i,@name,@child_id
exec kpi @child_id
fetch next from kk into @i,@name,@child_id
end
close kk
deallocate kk
-------------执行过程---------
exec kpi 1
:
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 = @idwhile(@@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)endreturn
END