dbo.uspGetWhereUsedProductID Stored Procedure

Description

Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.

Properties

Creation Date	08/01/2010 08:41
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@StartProductID	In	Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.	int	4
@CheckDate	In	Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.	datetime	4
@RETURN_VALUE	Return Value		int	4

Objects that dbo.uspGetWhereUsedProductID depends on

Database Object	Object Type	Description	Dep Level
Production.BillOfMaterials	Table	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.	1
dbo.Flag	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	2

Procedure Source Code

CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@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.[ProductAssemblyID] = p.[ProductID] 
WHERE b.[ComponentID] = @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 cte.[ProductAssemblyID] = b.[ComponentID]
INNER JOIN [Production].[Product] p 
ON b.[ProductAssemblyID] = 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;

Related Objects

dbo Schema
AdventureWorks Database