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
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
insert [Table] select 1, 0
union all select 2 ,1
union all select 3 ,2
union all select 4 ,2
union all select 5 ,3go
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
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
goselect [dbo].[fn_test](0)
select [dbo].[fn_test](1)
select [dbo].[fn_test](2)
/*结果
----------------------------------------------------------------------------------------------------
0,1,2,3,4,5(1 row(s) affected)
----------------------------------------------------------------------------------------------------
1,2,3,4,5(1 row(s) affected)
----------------------------------------------------------------------------------------------------
2,3,4,5(1 row(s) affected)
*/