ScrewTurn Wiki
Navigation
Main Page
Random Page
Create a new Page
All Pages
Categories
Administration
File Management
Login/Logout
Language Selection
Your Profile
Create Account
Quick Search
Advanced Search »
Back
History
dbo.uspGetWhereUsedProductID_(procedure)
== wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- valign="top" | '''Procedure''' | dbo.uspGetWhereUsedProductID |- valign="top" | '''Description''' | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. |} === Source === {{{{<nowiki> 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; </nowiki>}}}} === References === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' |- valign="top" | Select | Table | [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]] |- valign="top" | Select | Table | [[Production.Product_(table)|Production.Product]] |} == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure''' | dbo.uspGetWhereUsedProductID |- valign="top" | '''Description''' | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. |- |} {{{{<nowiki> 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; </nowiki>}}}} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' |- | Select | Table | [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]] |- | Select | Table | [[Production.Product_(table)|Production.Product]] |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.