-- =============================================   
-- Author: <David Gong>   
-- Create date: <2013-03-20>   
-- Description: <物料反展 参考邹健>   
-- =============================================
  ALTER   function   UF_ParentItem(@item   varchar(30))   
  returns   @ParentItem   table(ParentItem   varchar(30),[level]   int,ProcessedItem   varchar(8000) )   
  as   
  begin   
  declare   @l   int   
  set   @l=1   
  insert   @ParentItem   select   MD001,@l,MD001   
  from   BOMMD  a   
  where   MD003=@item   
  while   @@rowcount>0   
  begin   
  set   @l=@l+1   
  insert   @ParentItem   select   a.MD001,@l,rtrim(a.MD001)+' -> '+b.ProcessedItem   
  from   BOMMD a,@ParentItem  b   
  where   a.MD003=b.ParentItem   and   b.[level]=@l-1   
  end   
  update   @ParentItem   set   level=@l-level   
  return   
  end 
  
--顶层  
select   *   from   dbo.UF_ParentItem('31302002') WHERE level=1
--第二层
select   *   from   dbo.UF_ParentItem('31302002') WHERE level=2
--所有  
select   *   from   dbo.UF_ParentItem('31302002')