sql server 二表遞歸
ALTER Function [dbo].[Get_Bom] (
@factoryCode nvarchar(50), -- 'R11005'
@MATNR_FG nvarchar(50),
@M_VER NVARCHAR(10)
)
RETURNS @tb_Level TABLE(
MATNR_FG nvarchar(50),
M_VER NVARCHAR(10),
IDNRK NVARCHAR(50),
D_VER NVARCHAR(10) )
AS BEGIN
DECLARE @MATNR_FG1 nvarchar(50)
DECLARE @M_VER1 NVARCHAR(10)
DECLARE @IDNRK1 NVARCHAR(50)
DECLARE @D_VER1 NVARCHAR(10)
Declare @SumCount int
DECLARE curBom cursor for
select m.matnr_fg,m.m_ver, d.idnrk,d.idnrk_ver
from pubBomMaster as m left join
pubBomDetail as d on D.STKO_ID=M.STKO_ID
WHERE m.matnr_fg=@MATNR_FG AND M.M_VER=@M_VER AND m.WERKS=@factoryCode
OPEN CurBom
fetch next from CurBom into @MATNR_FG1,@M_VER1,@IDNRK1,@D_VER1
while @@fetch_status=0
begin
select @SumCount=Count(1)
from pubBomMaster
WHERE matnr_fg=@IDNRK1 AND M_VER=@D_VER1 AND WERKS=@factoryCode
IF (@SumCount>0 )
begin
INSERT INTO @tb_Level values(@MATNR_FG1,@M_VER1,@IDNRK1,@D_VER1)
insert into @tb_Level
SELECT * FROM dbo.Get_Bom(@factoryCode,@IDNRK1,@D_VER1)
end
ELSE
begin
INSERT INTO @tb_Level values(@MATNR_FG1,@M_VER1,@IDNRK1,@D_VER1)
end
fetch next from CurBom into @MATNR_FG1,@M_VER1,@IDNRK1,@D_VER1
end
close CurBom
deallocate CurBom
return
end
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/183242/viewspace-680912/ ，如需转载，请注明出处，否则将追究法律责任。
