# Procedure: uspGetBillOfMaterials

## Properties

Property | Value
--- | ---
Name | uspGetBillOfMaterials
ID | 119671474
Schema | dbo
Owner | dbo
Type | SQL STORED PROCEDURE
Execute As | Caller
Encrypted |
ANSI NULLS |
Uses Quoted Identifier |
Published |
Schema Published |
Execution Replicated |
Replication Serializable Only |
Skips Replication Constraints |
Is Recompiled |
Uses Database Collation |
Null on Null Input |
MS Shipped |
Modify Date | 8/8/2010 5:23:46 PM
Create Date | 8/8/2010 5:23:46 PM

## Extended Properties

Name | Value
--- | ---
MS_Description | Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.

## Permissions

No object level permissions defined.

## Parameters

Parameter Name | Datatype | Output | Cursor Ref | Readonly | Has Default Value | Default Value | Xml Collection Name | Description
--- | --- | --- | --- | --- | --- | --- | --- | ---
@StartProductID | int |  |  |  |  |  |  | Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.
@CheckDate | datetime |  |  |  |  |  |  | Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.

## Objects that depend on [uspGetBillOfMaterials]

- dbo.uspGetBillOfMaterials
  - HumanResources.ProcedureSynonym

## Objects on which [uspGetBillOfMaterials] depends

- dbo.uspGetBillOfMaterials
  - Production.BillOfMaterials
    - Production.Product
      - dbo.Flag
      - dbo.Name
      - Production.ProductModel
        - Production.ManuInstructionsSchemaCollection
        - Production.ProductDescriptionSchemaCollection
      - Production.ProductSubcategory
        - Production.ProductCategory
      - Production.UnitMeasure

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] @StartProductID [int], @CheckDate [datetime] AS BEGIN SET NOCOUNT ON; 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) ) 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; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetBillOfMaterials' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetBillOfMaterials', @level2type=N'PARAMETER',@level2name=N'@StartProductID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetBillOfMaterials', @level2type=N'PARAMETER',@level2name=N'@CheckDate'

#### See also

List of Procedures