Difference between revisions of "Dbo.uspGetWhereUsedProductID (procedure)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetWhereUsedProductID |- |} <pre> CREATE PROCEDURE [dbo].[uspGetWh...)
 
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== 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 ===
 +
<pre>
 +
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;
 +
</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 5: Line 74:
 
| '''procedure
 
| '''procedure
 
| dbo.uspGetWhereUsedProductID
 
| 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.
 
|-
 
|-
 
|}
 
|}
  
 
<pre>
 
<pre>
 +
 
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
 
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
 
     @StartProductID [int],
 
     @StartProductID [int],
Line 44: Line 117:
 
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]]
 +
 +
|}

Latest revision as of 23:15, 23 June 2010

wikibot[edit]

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[edit]

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[edit]

Dependency Type Object Type Referenced Object
Select Table Production.BillOfMaterials
Select Table Production.Product


automatically generated[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=Dbo.uspGetWhereUsedProductID_(procedure)&oldid=2592"