Recursive User-Defined Function

  • Not sure if it's relevant now, but in case... here's my code for an example where a list of Top Bills of Materials is used and a product structure table, to create a BOM explosion.

    (Thanks also go to Jeff M during its birth):

    ------------------------------------------------------------------------

    -- BOM explosion example, using a seed list

    -- Thanks to jhood for initial example, modified to explode a list of top bills of material for dm:

    -- Drop temp tables if they exist

    IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL

    DROP TABLE #dmBOM

    IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL

    DROP TABLE #dmBOM_expanded

    IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL

    DROP TABLE #dmBOM_final_output

    -- Input table is the product structure table

    -- Desired output is a table containing all unique parent and child parts

    -- Create the product structure table,

    -- each top bill of material with all of their child components

    CREATE TABLE #dmBOM (parent_item varchar(20), child_item varchar(20))

    INSERT INTO #dmBOM VALUES ('1', '2')

    INSERT INTO #dmBOM VALUES ('1', '3')

    INSERT INTO #dmBOM VALUES ('1', '4')

    INSERT INTO #dmBOM VALUES ('1', '5')

    INSERT INTO #dmBOM VALUES ('1', '6')

    INSERT INTO #dmBOM VALUES ('1', '7')

    INSERT INTO #dmBOM VALUES ('2', 'a')

    INSERT INTO #dmBOM VALUES ('2', 'b')

    INSERT INTO #dmBOM VALUES ('2', 'c')

    INSERT INTO #dmBOM VALUES ('3', '12')

    INSERT INTO #dmBOM VALUES ('3', '13')

    INSERT INTO #dmBOM VALUES ('3', '14')

    INSERT INTO #dmBOM VALUES ('4', 'x')

    INSERT INTO #dmBOM VALUES ('4', 'y')

    INSERT INTO #dmBOM VALUES ('4', 'z')

    INSERT INTO #dmBOM VALUES ('9', 'd')

    INSERT INTO #dmBOM VALUES ('9', 'e')

    INSERT INTO #dmBOM VALUES ('9', 'f')

    INSERT INTO #dmBOM VALUES ('a', 'g')

    INSERT INTO #dmBOM VALUES ('b', 'h')

    INSERT INTO #dmBOM VALUES ('c', 'i')

    -- Create the intermediate output table and initialise it with all the

    -- top bills of material that need exploding

    CREATE TABLE #dmBOM_expanded

    (item varchar(20), i_level INT)

    INSERT INTO #dmBOM_expanded (item, i_level)

    SELECT distinct parent_item, 0 AS i_level

    FROM #dmBOM

    -- Create and set a level counter, then explode the hierarchy using the product

    -- structure table, then remove duplicates to get the final output:

    DECLARE @CurrentLevel INT

    SET @CurrentLevel = 0

    WHILE @@ROWCOUNT > 0

    BEGIN

    SET @CurrentLevel = @CurrentLevel + 1

    INSERT INTO #dmBOM_expanded (item, i_level)

    SELECT p.child_item as item, @CurrentLevel AS i_level

    FROM #dmBOM as p

    INNER JOIN #dmBOM_expanded as h

    ON p.parent_item = h.item

    AND h.i_level = @CurrentLevel - 1

    END

    select distinct item

    into #dmBOM_final_output

    from #dmBOM_expanded

    order by item

    select * from #dmBOM order by parent_item, child_item

    select * from #dmBOM_expanded order by item, i_level

    select * from #dmBOM_final_output order by item

    -----------------------------------------------------------------------------------

    -- Cleanup temporary tables when verified

    IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL

    DROP TABLE #dmBOM

    IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL

    DROP TABLE #dmBOM_expanded

    IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL

    DROP TABLE #dmBOM_final_output

    -----------------------------------------------------------------------------------

    Regards, Dave

  • Hi GSquared,

    Are there any guidelines for an editorial as opposed to a post?

    Mike

  • MikeAngelastro-571287 (6/21/2011)


    Hi GSquared,

    Are there any guidelines for an editorial as opposed to a post?

    Mike

    Check the "Write for us" link on the left side of the site.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi GSquared,

    I sent a proposed article to the article email address for SQL Server Central on June 27 but haven't received any notification that it was received. How many days does it typically take to receive a status on article submissions?

    Thanks,

    Mike

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply