Stored procedures | SQL 2005 Adventure Works OLTP |
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
Syntax
dbo.uspGetWhereUsedProductID ( StartProductID, CheckDate )
Parameters
Variable Data Type Required Output Description StartProductID int Yes No Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table. CheckDate datetime Yes No Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.
Properties
Modifies data Transactional Uses cursors Executes procedures Dynamic content Encrypted No No No No No No
Data Source Activity
Accesses Description BOM_cte Production.BillOfMaterials Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components. Production.Product Products sold or used in the manfacturing of sold products.
Output Recordset
Column Description ProductAssemblyID ComponentID ComponentDesc TotalQuantity StandardCost ListPrice BOMLevel RecursionLevel
Data source Description BOM_cte
Applies to
Stored procedures | Returns data