ALTER function GetSubHYWithSelf
(
@p_HYID as int
)
returns @table table (id int,upid int,name varchar(50))
as
begin declare @rowscount int
declare @currentId int
declare @temptable table(Id int)
if exists(select * from hy where id=@p_HYID)--找自身
begin
insert into @table select * from hy where id=@p_HYID--添加自身
insert into @temptable select Id from hy where UpId=@p_HYID--找下级
set @rowscount=@@rowcount
while(@rowscount>0)
begin
set @rowscount=@rowscount-1
select @currentId= min(id) from @temptable
delete @temptable where id=@currentId
insert into @table select dbo.GetSubHYWithSelf(@currentId)
end
end
return
endselect * from dbo.GetSubHYWithSelf(1)
消息 4121,级别 16,状态 1,第 1 行
找不到列 "dbo" 或用户定义的函数或聚合 "dbo.GetSubHYWithSelf",或者名称不明确。不知道为什么会出现这个错误
如果注释了这一行insert into @table select dbo.GetSubHYWithSelf(@currentId) 则不会出现错误
(
@p_HYID as int
)
returns @table table (id int,upid int,name varchar(50))
as
begin declare @rowscount int
declare @currentId int
declare @temptable table(Id int)
if exists(select * from hy where id=@p_HYID)--找自身
begin
insert into @table select * from hy where id=@p_HYID--添加自身
insert into @temptable select Id from hy where UpId=@p_HYID--找下级
set @rowscount=@@rowcount
while(@rowscount>0)
begin
set @rowscount=@rowscount-1
select @currentId= min(id) from @temptable
delete @temptable where id=@currentId
insert into @table select dbo.GetSubHYWithSelf(@currentId)
end
end
return
endselect * from dbo.GetSubHYWithSelf(1)
消息 4121,级别 16,状态 1,第 1 行
找不到列 "dbo" 或用户定义的函数或聚合 "dbo.GetSubHYWithSelf",或者名称不明确。不知道为什么会出现这个错误
如果注释了这一行insert into @table select dbo.GetSubHYWithSelf(@currentId) 则不会出现错误
go
ALTER function GetSubHYWithSelf
(
@p_HYID as int
)
returns @table table (id int,upid int,name varchar(50))
as
begin declare @rowscount int
declare @currentId int
declare @temptable table(Id int)
if exists(select * from hy where id=@p_HYID)--找自身
begin
insert into @table select * from hy where id=@p_HYID--添加自身
insert into @temptable select Id from hy where UpId=@p_HYID--找下级
set @rowscount=@@rowcount
while(@rowscount>0)
begin
set @rowscount=@rowscount-1
select @currentId= min(id) from @temptable
delete @temptable where id=@currentId
insert into @table select dbo.GetSubHYWithSelf(@currentId)
end
end
return
end
go
select * from dbo.GetSubHYWithSelf(1)
(
@p_HYID as int
)
returns @table table (id int,upid int,name varchar(50))
as
begin declare @rowscount int
declare @currentId int
declare @temptable table(Id int)
if exists(select * from hy where id=@p_HYID)--找自身
begin
insert into @table select * from hy where id=@p_HYID--添加自身
insert into @temptable select Id from hy where UpId=@p_HYID--找下级
set @rowscount=@@rowcount
while(@rowscount>0)
begin
set @rowscount=@rowscount-1
select @currentId= min(id) from @temptable
delete @temptable where id=@currentId
insert into @table select * from dbo.GetSubHYWithSelf(@currentId) --改了这里
end
end
return
end