Difference between revisions of "Dbo.uspGetWhereUsedProductID (procedure)"
| Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Procedure | | '''Procedure | ||
| dbo.uspGetWhereUsedProductID | | dbo.uspGetWhereUsedProductID | ||
| Line 8: | Line 8: | ||
| '''Description | | '''Description | ||
| Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. | | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. | ||
| − | |||
|} | |} | ||
| − | |||
=== Source === | === Source === | ||
| Line 57: | Line 55: | ||
| '''Referenced Object | | '''Referenced Object | ||
| − | |- | + | |- valign="top" |
| Select | | Select | ||
| Table | | Table | ||
| − | | [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]] | + | | [[Production.BillOfMaterials_(_(table))|Production.BillOfMaterials]] |
| − | |- | + | |- valign="top" |
| Select | | Select | ||
| Table | | Table | ||
| − | | [[Production.Product_(table)|Production.Product]] | + | | [[Production.Product_(_(table))|Production.Product]] |
|} | |} | ||
Revision as of 22:59, 23 June 2010
wikibot
| Procedure | dbo.uspGetWhereUsedProductID |
| Description | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. |
Source
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@StartProductID [int],
@CheckDate [datetime]
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)
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)
)
-- 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;
References
| Dependency Type | Object Type | Referenced Object |
| Select | Table | Production.BillOfMaterials |
| Select | Table | Production.Product |
automatically generated
| procedure | dbo.uspGetWhereUsedProductID |
| Description | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. |
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@StartProductID [int],
@CheckDate [datetime]
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)
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)
)
-- 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;
| Dependency Type | Object Type | Referenced Object |
| Select | Table | Production.BillOfMaterials |
| Select | Table | Production.Product |
