CREATE function f_GetChild(@code int)
returns @t table(code int)
as
begin
declare @t_temp table(id int identity(1,1),child int)
insert into @t(code)
select Class_Id from Info_Class where Parent_Id = @codeinsert into @t_temp(child)
select Class_Id from Info_Class where Parent_Id= @codedeclare @child_temp int,@max_id int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(code)
select * from dbo.f_GetChild(@child_temp)
select @min_id = @min_id + 1
end
end
return
end
这是我从网上找的一个递归函数,执行后发现如果有记录的就会正常返回递归的值 如果没有记录就返回空。我想加一个判断 如果没有记录的时候就返回传进来的参数即返回@code 应该怎么办? 我曾试着在填充记录的时候用纪录数来判断却没有成功不知道为什么
returns @t table(code int)
as
begin
declare @t_temp table(id int identity(1,1),child int)
insert into @t(code)
select Class_Id from Info_Class where Parent_Id = @codeinsert into @t_temp(child)
select Class_Id from Info_Class where Parent_Id= @codedeclare @child_temp int,@max_id int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(code)
select * from dbo.f_GetChild(@child_temp)
select @min_id = @min_id + 1
end
end
return
end
这是我从网上找的一个递归函数,执行后发现如果有记录的就会正常返回递归的值 如果没有记录就返回空。我想加一个判断 如果没有记录的时候就返回传进来的参数即返回@code 应该怎么办? 我曾试着在填充记录的时候用纪录数来判断却没有成功不知道为什么
if not exists(select 1 from @t)
insert into @t
select @code
可这样考虑,在while后加思路是对的,但还缺少关健一环,怎么判断一次都未进入嵌套?
可设置一变量(declare @i int),在while前设置为(set @i=1)
在while中设为增1( set @i=@i+1) 若为1说明未进入嵌套
此时只要判断@max_id 或@min_id的值是否为空就可以了 (
if @max_id is null and @i=1
insert into @t select @code
也可不定义 @i 判断@min_id 但万一@t_temp中的min(id)=0就要了错了。
returns @t table(code int)
as
begin
declare @t_temp table(id int identity(1,1),child int)
insert into @t(code)
select Class_Id from Info_Class where Parent_Id = @codeinsert into @t_temp(child)
select Class_Id from Info_Class where Parent_Id= @codedeclare @child_temp int,@max_id int,@min_id int,@i int--加了,@i int
set @i=1 ---加的
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
set @i=@i+1 --加的
select @child_temp = child from @t_temp where id = @min_id
insert into @t(code)
select * from dbo.f_GetChild(@child_temp)
select @min_id = @min_id + 1
end
--end我去了(调不过去)
if @max_id is null and @i=1 --加的
insert into @t select @code --加的
return
end
(select Class_Id from Info_Class where Parent_Id = @code)取数
所以可用@max_id is null 来替代if not exists(select 1 from @t)