Latest revision |
Your text |
Line 1: |
Line 1: |
| == wikibot == | | == wikibot == |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- valign="top"
| |
− | | '''Procedure
| |
− | | dbo.uspGetBillOfMaterials
| |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
| |
− | |}
| |
− |
| |
− | === Source ===
| |
− | <pre>
| |
− | CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
| |
− | @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)
| |
− | -- 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;
| |
− | </pre>
| |
− |
| |
− | === 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" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
Line 76: |
Line 5: |
| | '''procedure | | | '''procedure |
| | dbo.uspGetBillOfMaterials | | | dbo.uspGetBillOfMaterials |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
| |
| |- | | |- |
| |} | | |} |
| | | |
| <pre> | | <pre> |
− |
| |
| CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] | | CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] |
| @StartProductID [int], | | @StartProductID [int], |
Line 121: |
Line 46: |
| END; | | END; |
| </pre> | | </pre> |
− |
| |
− |
| |
− | {| 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]]
| |
− |
| |
− | |}
| |