Recursive CTE for BOM Structures

  • Hi Guys,

    I am exploring the possibility of changing an inherited stored procedure to use Recursive CTE.

    Basically I have 2 tables - "InventoryMaster" and "Bomstructure" - the BomStructure contains "ParentPart, Component, QtyPer, RouteNo"

    The first stored procedure calls a second ;

    // ------------------------

    // BuildBOM1

    // ------------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[BuildBom1]

    AS

    IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_NAME = 'BOMTable') = 0

    BEGIN

    CREATE TABLE BOMTable(

    TopLevel [char] (30) NULL ,

    Parent_Rev [char] (10) NULL ,

    Level[int] NULL ,

    Assembly [char] (30) NULL ,

    Component[char] (30) NULL ,

    Component_Rev [char] (10) null,

    QtyPer [decimal](12, 6) NULL ,

    Route[char] (1) NULL

    )

    PRINT 'New table BOMTable added'

    END

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOMTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    delete from [BOMTable]

    DECLARE @StockCode CHAR(30)

    DECLARE MY_CURSOR CURSOR LOCAL FOR

    SELECT StockCode from InvMaster

    --where

    --( PartCategory = 'M' or PartCategory = 'G' ) and ComponentCount > 0 and

    --StockCode not in (select distinct Component from BomStructure)

    OPEN MY_CURSOR

    FETCH NEXT FROM MY_CURSOR into

    @StockCode

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Route '0'

    exec BuildBom2 @StockCode,@StockCode, '0' ,0

    --Route '1'

    exec BuildBom2 @StockCode,@StockCode, '1' ,0

    --Route '2'

    exec BuildBom2 @StockCode,@StockCode, '2' ,0

    --Route '3'

    exec BuildBom2 @StockCode,@StockCode, '3' ,0

    FETCH NEXT FROM MY_CURSOR into

    @StockCode

    END

    CLOSE MY_CURSOR

    DEALLOCATE MY_CURSOR

    select * from BOMTable

    //------------------------------------------------------------------------------------ End of Procedure

    //--------------------------------------------------

    // BuildBOM2

    // --------------------------------------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[BuildBom2] @TOPLevel CHAR(30), @AtLevel CHAR(30), @Route CHAR(1), @Level int

    AS

    SET @Level = @Level + 1

    DECLARE @ParentPart CHAR(30), @Component CHAR(30), @QtyPer Float

    DECLARE MY_CURSOR CURSOR LOCAL FOR

    SELECT ParentPart, Component, QtyPer, Route

    from BomStructure

    where ParentPart = @AtLevel AND

    Route = @Route

    OPEN MY_CURSOR

    FETCH NEXT FROM MY_CURSOR into

    @ParentPart, @Component, @QtyPer, @Route

    WHILE @@FETCH_STATUS = 0

    BEGIN

    Insert into BOMTable (TopLevel, Assembly,Component,QtyPer,Route,Level)

    values (@TOPLevel,@ParentPart, @Component, @QtyPer, @Route,@Level)

    EXEC BuildBom2 @TOPLevel, @Component, @Route, @Level

    FETCH NEXT FROM MY_CURSOR into

    @ParentPart, @Component, @QtyPer,@Route

    END

    CLOSE MY_CURSOR

    DEALLOCATE MY_CURSOR

    /* End of Procedure */

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • SteveEClarke (9/11/2012)


    Hi Guys,

    I am exploring the possibility of changing an inherited stored procedure to use Recursive CTE.

    Basically I have 2 tables - "InventoryMaster" and "Bomstructure" - the BomStructure contains "ParentPart, Component, QtyPer, RouteNo"

    The first stored procedure calls a second ;

    // ------------------------

    // BuildBOM1

    // ------------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[BuildBom1]

    AS

    IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_NAME = 'BOMTable') = 0

    BEGIN

    CREATE TABLE BOMTable(

    TopLevel [char] (30) NULL ,

    Parent_Rev [char] (10) NULL ,

    Level[int] NULL ,

    Assembly [char] (30) NULL ,

    Component[char] (30) NULL ,

    Component_Rev [char] (10) null,

    QtyPer [decimal](12, 6) NULL ,

    Route[char] (1) NULL

    )

    PRINT 'New table BOMTable added'

    END

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOMTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    delete from [BOMTable]

    DECLARE @StockCode CHAR(30)

    DECLARE MY_CURSOR CURSOR LOCAL FOR

    SELECT StockCode from InvMaster

    --where

    --( PartCategory = 'M' or PartCategory = 'G' ) and ComponentCount > 0 and

    --StockCode not in (select distinct Component from BomStructure)

    OPEN MY_CURSOR

    FETCH NEXT FROM MY_CURSOR into

    @StockCode

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Route '0'

    exec BuildBom2 @StockCode,@StockCode, '0' ,0

    --Route '1'

    exec BuildBom2 @StockCode,@StockCode, '1' ,0

    --Route '2'

    exec BuildBom2 @StockCode,@StockCode, '2' ,0

    --Route '3'

    exec BuildBom2 @StockCode,@StockCode, '3' ,0

    FETCH NEXT FROM MY_CURSOR into

    @StockCode

    END

    CLOSE MY_CURSOR

    DEALLOCATE MY_CURSOR

    select * from BOMTable

    //------------------------------------------------------------------------------------ End of Procedure

    //--------------------------------------------------

    // BuildBOM2

    // --------------------------------------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[BuildBom2] @TOPLevel CHAR(30), @AtLevel CHAR(30), @Route CHAR(1), @Level int

    AS

    SET @Level = @Level + 1

    DECLARE @ParentPart CHAR(30), @Component CHAR(30), @QtyPer Float

    DECLARE MY_CURSOR CURSOR LOCAL FOR

    SELECT ParentPart, Component, QtyPer, Route

    from BomStructure

    where ParentPart = @AtLevel AND

    Route = @Route

    OPEN MY_CURSOR

    FETCH NEXT FROM MY_CURSOR into

    @ParentPart, @Component, @QtyPer, @Route

    WHILE @@FETCH_STATUS = 0

    BEGIN

    Insert into BOMTable (TopLevel, Assembly,Component,QtyPer,Route,Level)

    values (@TOPLevel,@ParentPart, @Component, @QtyPer, @Route,@Level)

    EXEC BuildBom2 @TOPLevel, @Component, @Route, @Level

    FETCH NEXT FROM MY_CURSOR into

    @ParentPart, @Component, @QtyPer,@Route

    END

    CLOSE MY_CURSOR

    DEALLOCATE MY_CURSOR

    /* End of Procedure */

    It would help if you posted the DDL for the tables, some sample data for the tables (as a series of insert into statements), and the expecte results based on the sample data you provide.

  • .

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • SteveEClarke (9/11/2012)


    Ths is the actual resultset ( from current SP's )

    TopLevel LevelAssembly Component QtyPer Route

    CD150M-BS-020 1CD150M-BS-020 CD150-CI-ACW-16 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 11-0386-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0282-3065 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 11-0259-0125 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0283-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0430-8412 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 43-0763-9912 10.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0445-8132 6.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0435-8412 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0284-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1004-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-0620-9913 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-0700-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 41-0586-9911 1.0000000

    CD150M-BS-020 341-0586-9911 41-0230-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1445-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1446-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 28-0255-8011 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1036-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 41-0241-6112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 43-1506-8112 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 54-1131-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0230-3612 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0201-6162 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 21-0230-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 23-0586-9923 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0320-8412 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0301-8762 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0330-8418 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0301-8172 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0440-8132 6.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0401-8762 12.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0401-8182 12.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0644-8132 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0601-8762 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0601-8182 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 47-0148-7913 1.0000000

    CD150M-BS-020 347-0148-7913 47-0332-6113 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0420-8412 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5413 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5423 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5433 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5443 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6513 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6523 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6533 1.0000000

    CD150M-BS-020 1CD150M-BS-020 MA1242 1.0000000

    CD150M-BS-020 2MA1242 13-0174-0215 1.0000000

    CD150M-BS-020 2MA1242 16-0400-8041 1.0000000

    CD150M-BS-020 2MA1242 39-0069-9912 2.0000000

    CD150M-BS-020 2MA1242 13-0172-8811 1.0000000

    CD150M-BS-020 2MA1242 41-0232-9912 1.0000000

    CD150M-BS-020 2MA1242 38-0809-4112 2.0000000

    CD150M-BS-020 2MA1242 13-0173-0115 1.0000000

    CD150M-BS-020 2MA1242 35-0225-8412 3.0000000

    CD150M-BS-020 2MA1242 35-0201-8762 3.0000000

    CD150M-BS-020 2MA1242 51-0003-9912 1.0000000

    CD150M-BS-020 2MA1242 51-0010-9912 1.0000000

    CD150M-BS-020 2MA1242 43-1048-9912 1.0000000

    CD150M-BS-020 2MA1242 43-1788-9912 1.0000000

    CD150M-BS-020 2MA1242 38-1033-4112 1.0000000

    CD150M-BS-020 2MA1242 45-0281-8812/170 1.0000000

    CD150M-BS-020 345-0281-8812/170 45-0281-8812 0.2000000

    CD150M-BS-020 2MA1242 36-0650-9913 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9923 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9933 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9943 2.0000000

    CD150M-BS-020 2MA1242 36-0650-9953 1.0000000

    CD150M-BS-020 2MA1242 43-0733-9912 1.0000000

    CD150M-BS-020 1CD150M-BS-020 V150-14 1.0000000

    CD150M-BS-020 2V150-14 23-0590-0125 1.0000000

    CD150M-BS-020 2V150-14 39-0652-1213 1.0000000

    CD150M-BS-020 2V150-14 23-0591-4113 1.0000000

    CD150M-BS-020 2V150-14 33-9560-8132 4.0000000

    CD150M-BS-020 2V150-14 33-0501-8182 4.0000000

    CD150M-BS-020 2V150-14 35-0501-8762 4.0000000

    CD150M-BS-020 2V150-14 23-0592-0125 1.0000000

    CD150M-BS-020 2V150-14 38-0961-4112 1.0000000

    CD150M-BS-020 2V150-14 33-9540-8132 4.0000000

    CD150M-BS-020 2V150-14 28-0130-9912 4.0000000

    CD150M-BS-020 2V150-14 54-0923-9912 1.0000000

    CD150M-BS-020 2V150-14 33-0648-8132 8.0000000

    CD150M-BS-020 2V150-14 33-0601-8182 8.0000000

    CD150M-BS-020 2V150-14 33-0601-8762 8.0000000

    CD150M-BS-020 1CD150M-BS-020 EP25-27 1.0000000

    CD150M-BS-020 2EP25-27 23-0568-2016 1.0000000

    CD150M-BS-020 2EP25-27 23-0571-2016 1.0000000

    CD150M-BS-020 2EP25-27 38-1013-4112 1.0000000

    CD150M-BS-020 2EP25-27 38-1014-4112 1.0000000

    CD150M-BS-020 2EP25-27 23-0570-2011 1.0000000

    CD150M-BS-020 2EP25-27 23-0567-2011 1.0000000

    CD150M-BS-020 2EP25-27 23-0412-2011 1.0000000

    CD150M-BS-020 2EP25-27 39-0641-4113 1.0000000

    CD150M-BS-020 1CD150M-BS-020 AC150-49 1.0000000

    CD150M-BS-020 2AC150-49 49-0523-9912 1.0000000

    CD150M-BS-020 2AC150-49 38-1071-5613 1.0000000

    CD150M-BS-020 2AC150-49 35-0330-8412 4.0000000

    CD150M-BS-020 2AC150-49 35-0301-8762 8.0000000

    CD150M-BS-020 2AC150-49 26-1114-9912 1.0000000

    CD150M-BS-020 2AC150-49 26-1263-9912 1.0000000

    CD150M-BS-020 2AC150-49 26-1305-9915 1.0000000

    CD150M-BS-020 2AC150-49 26-1407-9912 1.0000000

    CD150M-BS-020 2AC150-49 33-0701-8762 1.0000000

    CD150M-BS-020 2AC150-49 54-0925-9915 1.0000000

    CD150M-BS-020 354-0925-9915 54-0556-9912 1.0000000

    CD150M-BS-020 2AC150-49 27-2806-8211 1.0000000

    CD150M-BS-020 2AC150-49 43-1204-7015 1.0000000

    CD150M-BS-020 343-1204-7015 43-1204-7004 1.0000000

    CD150M-BS-020 2AC150-49 43-1483-8112 1.0000000

    CD150M-BS-020 2AC150-49 43-0718-9913 1.0000000

    CD150M-BS-020 2AC150-49 43-1632-8112 1.0000000

    CD150M-BS-020 2AC150-49 27-2805-9821 1.0000000

    CD150M-BS-020 2AC150-49 35-0399-8402/110 4.0000000

    CD150M-BS-020 2AC150-49 27-2775-8515 1.0000000

    CD150M-BS-020 2AC150-49 35-0345-8132 4.0000000

    CD150M-BS-020 2AC150-49 35-0112-8412 2.0000000

    CD150M-BS-020 2AC150-49 35-0101-8182 1.0000000

    CD150M-BS-020 2AC150-49 35-0101-8762 2.0000000

    CD150M-BS-020 2AC150-49 54-0923-9912 1.0000000

    CD150M-BS-020 2AC150-49 43-0686-9912 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6513 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6523 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6533 1.0000000

    CD150M-BS-020 2AC150-49 43-1473-8112 1.0000000

    CD150M-BS-020 2AC150-49 43-0733-9912 1.0000000

    CD150M-BS-020 1CD150M-BS-020 SUPP-20 1.0000000

    CD150M-BS-020 2SUPP-20 27-2796-8211 1.0000000

    CD150M-BS-020 2SUPP-20 35-0335-8412 2.0000000

    CD150M-BS-020 2SUPP-20 35-0301-8182 2.0000000

    CD150M-BS-020 2SUPP-20 35-0301-8762 2.0000000

    CD150M-BS-020 1CD150M-BS-020 ACC-L-26 1.0000000

    CD150M-BS-020 2ACC-L-26 47-0344-7911 1.0000000

    CD150M-BS-020 347-0344-7911 47-0332-6113 1.0000000

    CD150M-BS-020 1CD150M-BS-020 ACC-L-23 1.0000000

    CD150M-BS-020 2ACC-L-23 34-0721-9912 4.0000000

    Not much help. Need the DDL (CREATE TABLE statements) for the the source tables. Some sample data (as a series of INSERT INTO statements) for each table. Expected results based on the sample data you provide.

  • Ok - sorry about this ....

    data to be populated - Master table - InvSteve, link to BomSteve

    drop table InvSteve

    go

    drop table BomSteve

    go

    create table InvSteve

    (

    StockCode [char] (30) NULL,

    Rev [char] (2) NULL

    )

    Insert into InvSteve (StockCode, Rev)

    SELECT 'CD150M-BS-020','A'

    UNION ALL

    SELECT 'Test_Part_2','B'

    UNION ALL

    SELECT 'Test_Part_3','XX'

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

    create table BomSteve

    (

    ParentPart [char] (30) NULL,

    Component [char] (30) NULL,

    QtyPer [decimal] (12,6) NULL,

    Route [char] (1) NULL

    )

    Insert into BomSteve (ParentPart, Component, QtyPer, Route )

    SELECT 'CD150M-BS-020','CD150-CI-ACW-16',1.000000, 0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','11-0386-0115',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','12-0282-3065',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','11-0259-0125',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','12-0283-0115',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','35-0430-8412',4.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','43-0763-9912',10.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','35-0445-8132',6.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','35-0435-8412',4.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','12-0284-0115',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1004-4112',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-0620-9913',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-0700-4112',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','41-0586-9911',1.000000,0

    UNION ALL

    SELECT '41-0586-9911','41-0230-9912',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1445-9912',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1446-9912',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','28-0255-8011',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','33-0601-8762',8.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','33-0601-8182',8.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','47-0148-7913',1.000000,0

    UNION ALL

    SELECT '47-0148-7913','47-0332-6113',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1034-5413',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1034-5423',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1034-5433',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','38-1034-5443',2.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','36-0646-6513',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','36-0646-6523',1.000000,0

    UNION ALL

    SELECT 'CD150-CI-ACW-16','36-0646-6533',1.000000,0

    UNION ALL

    SELECT 'CD150M-BS-020','SUPP-20',1.000000,0

    UNION ALL

    SELECT 'SUPP-20','27-2796-8211',1.000000,0

    UNION ALL

    SELECT 'SUPP-20','35-0335-8412',2.000000,0

    UNION ALL

    SELECT 'SUPP-20','35-0301-8182',2.000000,0

    UNION ALL

    SELECT 'SUPP-20','35-0301-8762',2.000000,0

    UNION ALL

    SELECT 'CD150M-BS-020','ACC-L-26',1.000000,0

    UNION ALL

    SELECT 'ACC-L-26','47-0344-7911',1.000000,0

    UNION ALL

    SELECT '47-0344-7911','47-0332-6113',1.000000,0

    UNION ALL

    SELECT 'CD150M-BS-020','ACC-L-23',1.000000,0

    UNION ALL

    SELECT 'ACC-L-23','34-0721-9912',4.000000,0

    UNION ALL

    SELECT 'Test_Part_2','Test_Part_3',99, 1

    UNION ALL

    SELECT 'Test_Part_3','NO_MORE_COMPONENTS',14,1

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

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • Now, based on the data above, what is the expected output?

  • I'm not sure if this gives the correct results. I can't compare it to anything but it should give you something to start with.

    ;WITH Sample AS(

    SELECTI.StockCode,

    B.ParentPart,

    B.Component,

    B.QtyPer,

    B.Route,

    0 AS [level]

    from #BomSteve B

    JOIN #InvSteve I ON I.StockCode = B.ParentPart

    UNION ALL

    SELECTs.StockCode,

    B.ParentPart,

    B.Component,

    B.QtyPer,

    B.Route,

    s.[level] + 1 AS [level]

    from Sample s

    JOIN #BomSteve B ON s.Component = B.ParentPart

    )

    SELECT *

    FROM Sample

    ORDER BY StockCode, [level], ParentPart, Component

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Brilliant - that is exactly what I was after.

    Thank you for your assistance

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

Viewing 8 posts - 1 through 7 (of 7 total)

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