34,576
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE dbo.GetFamilyParts
(
@node int
)
AS
SET NOCOUNT ON
select a.* from Part a,dbo.GetPartsFamily(@node) b where a.ID=b.Part
return
ALTER PROCEDURE dbo.GetPartsFamily
(
@id int
)
AS
SET NOCOUNT ON
declare @t table (id int,Level int)
declare @Level int
set @Level=0
insert @t values(@id,@Level)
while exists (select 1
from @t t,PartsClass a
where a.Parent=t.id
and t.Level=@Level
)
begin
insert @t select a.id,@Level+1
from @t t,PartsClass a
where a.Parent=t.id
and t.Level=@Level
set @Level = @Level+1
end
Select a.* from PartsClass a,@t t where a.ID=t.id
return
--不對,可以將存儲過程結果放進臨時表 然後join
create table #t (collist ....)
insert #t exec dbo.GetPartsFamily(@node)
select * .....