SQL Server RETURN in order recursive query

Question

I have a recursive query which outputs a BOM (Bill of Materials) and it does output all of the levels. however the problem is that it is out of order when the procedure is called this is in either access and SQL Server. And if there are no child parts, then the part is nothing is returned.

Code

Create/Alter table:

USE [<Database>]

SET ANSI_NULLS ON
GO    

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE BOM
(
    [POSITION] INT CHECK ([POSITION] >= 0),
    [PARENT] VARCHAR(10),
    [PART] VARCHAR(10),
    [QUANTITY] INT NOT NULL
) ON [PRIMARY]
GO

Create/Alter procedure:

USE [<Database>]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description: GETS BOM GIVEN PARAMETER
-- =============================================
CREATE PROCEDURE GETBOM 
    @TL VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;

    WITH TESTBOM AS
    (
        SELECT 
            [POSITION], [PARENT], [PART], [QUANTITY]
        FROM 
            BOM
        WHERE  
            [PART] = @TL 
            AND [Parent] IS NULL
        UNION ALL
        SELECT 
            E.[POSITION], E.[PARENT], E.[PART], E.[QUANTITY]
        FROM 
            BOM E  
        INNER JOIN 
            TESTBOM M ON E.[PARENT] = M.[PART] 
    )
    SELECT * FROM TESTBOM
END
GO

Sample data:

POSITION	PARENT	PART	QUANTITY
0		A	1
1	A	B	1
0		B	1
1	B	E	1
2	B	F	1
0		F	1
1	F	K	1
2	F	C	1
0		C	1
1	C	G	2
2	C	H	1
2	A	C	1
3	A	D	1
0		D	1
1	D	I	1
0		I	1
1	I	L	1
2	I	M	2
2	D	J	1

Outputs

A:

EXEC GETBOM 'A' 

B:

EXEC GETBOM 'B' 

C

EXEC GETBOM 'C' 

D

EXEC GETBOM 'D' 

E

EXEC GETBOM 'E' 

Answer 1

The following code assembles a hierarchyid as it walks down through assembling the BoM recursively (dbfiddle).

Sample data:

-- Sample data.
declare @BoM as table (
  Position Int Check ( Position >= 0 ),
  Parent VarChar(10),
  Part VarChar(10),
  Quantity Int not NULL );

insert into @BoM ( Position, Parent, Part, Quantity ) values
  ( 0, NULL, 'A', 1 ),
  ( 1, 'A', 'B', 1 ),
  ( 0, NULL, 'B', 1 ),
  ( 1, 'B', 'E', 1 ),
  ( 2, 'B', 'F', 3 ), -- Changed quantity from 1 to 3 to show extended quantity.
  ( 0, NULL, 'F', 1 ),
  ( 1, 'F', 'K', 1 ),
  ( 2, 'F', 'C', 1 ),
  ( 0, NULL, 'C', 1 ),
  ( 1, 'C', 'G', 2 ),
  ( 2, 'C', 'H', 1 ),
  ( 2, 'A', 'C', 1 ),
  ( 3, 'A', 'D', 1 ),
  ( 0, NULL, 'D', 1 ),
  ( 1, 'D', 'I', 1 ),
  ( 0, NULL, 'I', 1 ),
  ( 1, 'I', 'L',  1 ),
  ( 2, 'I', 'M', 2 ),
  ( 2, 'D', 'J', 1 );

select * from @BoM;

Recursive query:

-- Starting point for building a BoM.
declare @BasePart as VarChar(10) = 'A';

-- Do the deed.
with
  BoMTree as (
    -- Start with the parentless base part ...
    select Position, Parent, Part, Quantity,
      -- Many BoMs provide the extended quantity for subcomponents.
      Quantity as ExtendedQuantity,
      -- Build a hierarchy to keep track of the order of parts within the BoM.
      Cast( '/' + Cast( Row_Number() over ( order by Position ) as VarChar(4) ) + '/' as VarChar(100) ) as BoMOrder
      from @BoM
      where Part = @BasePart and Parent is NULL
    union all
    -- ... and add additional levels of parts one at a time.
    select B.Position, B.Parent, B.Part, B.Quantity,
      -- Extend the quantity.
      B.Quantity * BT.ExtendedQuantity,
      -- Add another level to the hierarchy.
      Cast( BT.BoMOrder + Cast( Row_Number() over ( order by B.Position ) as VarChar(4) ) + '/' as VarChar(100) ) as BoMOrder
      from @BoM as B inner join
        BoMTree as BT on B.Parent = BT.Part
    ),
  BoMTreeWithOrder as (
    -- Cast the order from a string to a proper hierarchy id.
    select Position, Parent, Part, Quantity, ExtendedQuantity,
      Cast( BoMOrder as HierarchyId ) as BoMOrderHI
      from BoMTree )
  -- Display the indented BoM.
  select Position, Parent,
    Space( ( BoMOrderHI.GetLevel() - 1 ) * 2 ) + Part as Part,
    Quantity,
    ExtendedQuantity, BoMOrderHI
    from BomTreeWithOrder
    order by BoMOrderHI;

Answer 2

It's hard to say exactly what you need here without proper sample data, but it seems if you concatenate each item's position with all its parents, you can order by that.

CREATE OR ALTER PROCEDURE GETBOM 
@TL VARCHAR(10)

AS

SET NOCOUNT ON;

WITH TESTBOM AS
(
    SELECT POSITION, PARENT, PART, QUANTITY, CAST(POSITION AS nvarchar(100)) AS Ordering
    FROM BOM
    WHERE [PART] = @TL
      and [Parent] is null

    UNION ALL

    SELECT E.[POSITION],E.[PARENT],E.[PART],E.[QUANTITY], CAST(CONCAT(M.Ordering, E.POSITION) AS nvarchar(200)) 
    FROM BOM E
    INNER JOIN TESTBOM M ON E.[PARENT]=M.[PART] 
)
SELECT
  POSITION, PARENT, PART, QUANTITY
FROM TESTBOM
ORDER BY
  Ordering;