-----測試數據
Create Table PDM_MatGroup(MatGroup varchar(16))
Create Table PDM_MatGroupItems(MatGroup varchar(16),SerialNum int,MatCode varchar(16))
Insert PDM_MatGroup
Select 'T05E8482B1000001'
Insert PDM_MPDM_MatGroupItemstGroup
Select 'T05E8482B1000001',1,'774SD1000101'
Union
Select 'T05E8482B1000001',2,'T05A8482B1000001'
Union
Select 'T05A8482B1000001',1,'G3113114103S1'
-----測試數據
-----函數
Create Function PDM_MatLevel(@MatCode cPartNum,@LevelNo int,@LevelCount int )
Returns @T Table(PartNum varchar(16),LevelNo int,MatCode varchar(16),IsLevel int)
AS
Begin
--Write FirstLevel
IF @LevelNo = 0
Begin
Insert @T(PartNum,LevelNo,MatCode,IsLevel)
Select @MatCode,0,@MatCode,0
From PDM_MatGroup(nolock)
Where MatGroup = @MatCode
End
Declare @LevelNext int
Select @LevelNext=@LevelNo+1
IF @LevelNext > @LevelCount
Begin
Return
End
--Level is true
IF exists(Select t2.MatCode
From @T t1,
PDM_MatGroupItems t2(nolock)
Where t1.LevelNo= @LevelNo
and t1.MatCode=t2.MatGroup)
Begin
--Set LevelTree
Update t1
Set t1.IsLevel = 1
From @T t1,PDM_MatGroupItems t2(nolock)
Where t1.LevelNo = @LevelNo
and t1.MatCode = t2.MatGroup
--Write NextLevel
Insert @T(PartNum,LevelNo,MatCode,IsLevel)
Select @MatCode,@LevelNext,t2.MatCode,0
From @T t1,
PDM_MatGroupItems t2(nolock)
Where t1.LevelNo = @LevelNo
and t1.MatCode = t2.MatGroup
IF @@NESTLEVEL <32
Insert @T Select * From dbo.PDM_MatLevel(@MatCode,@LevelNext,@LevelCount)
End
Return
End
-----函數
----查詢
Select * From PDM_MatLevel('T05E8482B1000001',0,255)
結果只跑了1層,第2層沒跑,谁能幫忙看下哪裡有問題,謝謝
|
