--试试这个
if exists(select name from sysobjects where name='test'and type='U')
drop table test
go
create table test(id int not null,pid int not null)
go
insert into test
select 1,0
union select 2 ,1
union select 3,2
union select 4,2
union select 5,3
go
if exists(select name from sysobjects where name='fn_test'and type='FN')
drop function fn_test
go
create function fn_test
(
@pID int
)
returns varchar(100)
as
begin
declare @return varchar(100);
set @return=cast(@pID as varchar(5));
with result(id,pid)
as
(
select id,pid from test where pid=@pID
union all
select b.id,b.pid from result a inner join test b on a.id=b.pid
)
select @return=@return+','+cast(id as varchar(5)) from result;
return @return
end
go
create table [Table](id int,pid int)
insert [Table] select 1, 0
union all select 2 ,1
union all select 3 ,2
union all select 4 ,2
union all select 5 ,3
go
create function fn_GetNode(@id int)
returns @a table(a int)
as
begin
declare @x table(a int)
declare @y table(a int)
insert @a select @id
insert @x select id from [Table] where pid=@id
insert @a select * from @x
while @@rowcount>0
begin
insert @a select id from [Table] where pid in(select * from @x)
insert @y select id from [Table] where pid in(select * from @x)
delete from @x
insert @x select * from @y
delete from @y
end
return
end
go
declare @y varchar(100)
select @y=isnull(@y+',','')+ltrim(a) from dbo.fn_getnode(0) order by a
select @y
create table tb(
id int not null,
pid int not null)
go
insert into tb select 1,0
union select 1,0
union select 2 ,1
union select 3,2
union select 4,2
union select 5,3
go
create function ge(@a int)
returns varchar(200) as
begin
declare @s varchar(200)
declare @i int
declare @p int
declare @cur cursor
set @s=Convert(varchar(20),@a)
select @i=count(*) from tb where pid=@a
if @i=0
return @s
set @p=@a
if @i>0
begin
--游标操作
set @cur=cursor LOCAL SCROLL for select id from tb where pid=@p
open @cur
Fetch next from @cur into @P
while @@FETCH_STATUS=0
BEGIN
SET @S=@S+','+DBO.GE(@P)
Fetch next from @cur into @P
END
close @cur
DEALLOCATE @cur
end
return @s
end
go
select dbo.ge(0)
select dbo.ge(2)
drop table tb
drop function ge