SQL Server 2000 递归遍历查询ERP系统中的BOM信息

ERP系统用的数据库比较老，SQL Server 2000,

需要查询遍历所有层级的BOM信息。

应该适用于FAS2000系列、SUNLIKE系列、天心、天思、有利、GXXX KERP等同系源ERP产品。

CREATE  FUNCTION F_GETBOM(@BOM_NO VARCHAR(100))
RETURNS @BOM TABLE
    (
        [BOM_NO] [VARCHAR] (38) COLLATE COMPATIBILITY_198_804_30001 NOT NULL ,
        [ITM] [SMALLINT] NOT NULL ,
        [PRD_NO] [VARCHAR] (30) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [PRD_MARK] [VARCHAR] (40) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [ID_NO] [VARCHAR] (38) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [NAME] [VARCHAR] (100) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [WH_NO] [VARCHAR] (12) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [BOM_ID] [VARCHAR] (1) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [UNIT] [VARCHAR] (1) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [QTY] [NUMERIC](28, 8) NULL ,
        [QTY1] [NUMERIC](28, 8) NULL ,
        [LOS_RTO] [NUMERIC](28, 8) NULL ,
        [CST] [NUMERIC](28, 8) NULL ,
        [PRD_NO_UP] [VARCHAR] (30) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [ID_NO_UP] [VARCHAR] (38) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [EXP_ID] [VARCHAR] (1) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [PRD_NO_CHG] [VARCHAR] (200) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [REM] [VARCHAR] (200) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [START_DD] [DATETIME] NULL ,
        [END_DD] [DATETIME] NULL ,
        [ZC_NO] [VARCHAR] (10) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [TW_ID] [VARCHAR] (1) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [USEIN_NO] [VARCHAR] (200) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [QTY_BAS] [NUMERIC](28, 8) NULL ,
        [PZ_ID] [VARCHAR] (30) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [COMPOSE_IDNO] [VARCHAR] (50) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [UP_STD] [NUMERIC](28, 8) NULL ,
        [UP_TAX] [NUMERIC](28, 8) NULL ,
        [CUS_NO] [VARCHAR] (12) COLLATE COMPATIBILITY_198_804_30001 NULL ,
        [RTO_TAX] [NUMERIC](28, 8) NULL ,
        [LEVEL1] [INTEGER],
        [BOM_PATH] [VARCHAR](1000)    
    )
AS

BEGIN
    DECLARE @G_LEVEL INT
    SET @G_LEVEL=0
        --写入BOM根目录
        INSERT @BOM 
        (
            BOM_NO,ITM,PRD_NO,PRD_MARK,ID_NO,NAME,WH_NO,BOM_ID,UNIT,
            QTY,QTY1,LOS_RTO,CST,PRD_NO_UP,ID_NO_UP,EXP_ID,PRD_NO_CHG,
            REM,START_DD,END_DD,ZC_NO,TW_ID,USEIN_NO,QTY_BAS,PZ_ID,
            COMPOSE_IDNO,UP_STD,UP_TAX,CUS_NO,RTO_TAX,
            LEVEL1,BOM_PATH 
        )         
        SELECT 
            A.BOM_NO,A.ITM,A.PRD_NO,A.PRD_MARK,A.ID_NO,A.NAME,A.WH_NO,A.BOM_ID,A.UNIT,
            A.QTY,A.QTY1,A.LOS_RTO,A.CST,A.PRD_NO_UP,A.ID_NO_UP,A.EXP_ID,A.PRD_NO_CHG,
            A.REM,A.START_DD,A.END_DD,A.ZC_NO,A.TW_ID,A.USEIN_NO,A.QTY_BAS,A.PZ_ID,
            A.COMPOSE_IDNO,A.UP_STD,A.UP_TAX,A.CUS_NO,A.RTO_TAX,
            @G_LEVEL LEVEL1    ,@BOM_NO BOM_PATH 
        FROM 
            TF_BOM A
        WHERE 
            --A.BOM_NO='300000RF712A-1->'
            A.BOM_NO=@BOM_NO
        --遍历BOM，写入所有的物料和子件BOM信息        
        WHILE @@ROWCOUNT>0
        BEGIN
            SET @G_LEVEL=@G_LEVEL+1
            
            INSERT @BOM 
            (
                BOM_NO,ITM,PRD_NO,PRD_MARK,ID_NO,NAME,WH_NO,BOM_ID,UNIT,
                QTY,QTY1,LOS_RTO,CST,PRD_NO_UP,ID_NO_UP,EXP_ID,PRD_NO_CHG,
                REM,START_DD,END_DD,ZC_NO,TW_ID,USEIN_NO,QTY_BAS,PZ_ID,
                COMPOSE_IDNO,UP_STD,UP_TAX,CUS_NO,RTO_TAX,
                LEVEL1,BOM_PATH
            ) 
            SELECT 
                A.BOM_NO,A.ITM,A.PRD_NO,A.PRD_MARK,A.ID_NO,A.NAME,A.WH_NO,A.BOM_ID,A.UNIT,
                A.QTY,A.QTY1,A.LOS_RTO,A.CST,A.PRD_NO_UP,A.ID_NO_UP,A.EXP_ID,A.PRD_NO_CHG,
                A.REM,A.START_DD,A.END_DD,A.ZC_NO,A.TW_ID,A.USEIN_NO,A.QTY_BAS,A.PZ_ID,
                A.COMPOSE_IDNO,A.UP_STD,A.UP_TAX,A.CUS_NO,A.RTO_TAX,
                @G_LEVEL LEVEL1,
                BOM_PATH COLLATE COMPATIBILITY_198_804_30001+'→'+A.BOM_NO COLLATE COMPATIBILITY_198_804_30001 BOM_PATH
            FROM 
                TF_BOM A,@BOM B
            WHERE 
                A.BOM_NO = B.ID_NO COLLATE COMPATIBILITY_198_804_30001
                AND B.LEVEL1 = @G_LEVEL-1 
        END
    --返回BOM表
    RETURN
END


--查询结果
SELECT BOM_PATH,* FROM DBO.F_GETBOM('3000009812A-1->') ORDER BY BOM_PATH,ITM