Execute the following T-SQL scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the architecture of a stored procedure with tree processing (recursive common table expression, recursive CTE).

-- Microsoft SQL Server stored procedure with recursive TREE processing
-- MSSQL find all components needed for an assembly
-- T-SQL bill of materials processing
USE AdventureWorks;
GO
CREATE PROC AssemblyComposition
           @AssemblyID INT
AS
  BEGIN
    WITH cteAssembly(AssemblyID,ComponentID,PerAssemblyQty,ComponentLevel)
 -- Root (anchor) part
        AS (SELECT b.ProductAssemblyID,
                    b.ComponentID,
                    b.PerAssemblyQty,
                    0 AS ComponentLevel
             FROM   Production.BillOfMaterials AS b
             WHERE  b.ProductAssemblyID = @AssemblyID
-- Recursive part - recursive CTE
             UNION ALL
             SELECT bom.ProductAssemblyID,
                    bom.ComponentID,
                    p.PerAssemblyQty,
                    ComponentLevel + 1
             FROM   Production.BillOfMaterials AS bom
                    INNER JOIN cteAssembly AS p
                      ON bom.ProductAssemblyID = p.ComponentID)
    SELECT   ASSEMBLY = p1.Name,
             a.ComponentID,
             Component = p2.Name,
             a.PerAssemblyQty,
             ComponentLevel
    FROM     Production.Product p1
             INNER JOIN cteAssembly AS a
               ON a.AssemblyID = p1.ProductID
             INNER JOIN Production.Product AS p2
               ON a.ComponentID = p2.ProductID
    ORDER BY ComponentLevel,
             ASSEMBLY,
             Component;
  END
 
GO
-- T-SQL TOP function & newid function for random results
SELECT TOP (5) ProductID, ProductName=Name, ListPrice
FROM Production.Product ORDER BY NEWID()
GO
/*
ProductID   ProductName                   ListPrice
999         Road-750 Black, 52            539.99
317         LL Crankarm                   0.00
867         Women's Mountain Shorts, S    69.99
930         HL Mountain Tire              35.00
820         HL Road Front Wheel           330.06
*/
 
-- Assembling HL Road Front Wheel
-- Execute stored procedure with recursive tree processing
EXEC AssemblyComposition   999
GO
/* Results
 

*/
 

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