Properties

Owner: dbo 
Encrypted:  
Creation Date: 04/26/2006 
Modification Date:  
Description: Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID. 

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length Default Description
  @StartProductID  INPUT  int    Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table. 
  @CheckDate  INPUT  datetime    Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date. 
  @name  INPUT  nvarchar  50     
  @name2  INPUT  varchar  50     
Total: 4 parameter(s)

Objects that [dbo].[uspGetBillOfMaterials] depends on

Object Name Owner Object Type Dep Level
  Flag  dbo  User Defined type 
  Name  dbo  User Defined type 
  ProductCategory  Production  Table 
  ProductModel  Production  Table 
  UnitMeasure  Production  Table 
  ProductSubcategory  Production  Table 
  Product  Production  Table 
  BillOfMaterials  Production  Table 
Total: 8 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime],
    @name as nvarchar(50),
    @name2 as varchar(50)

AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE b.[ProductAssemblyID] = @StartProductID
            AND @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures