datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find all the components of a mountain bike recursively?

Execute the following T-SQL scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the architecture of a stored procedure with tree processing (recursive common table expression, recursive CTE).

-- Microsoft SQL Server stored procedure with recursive TREE processing
-- MSSQL find all components needed for an assembly
-- T-SQL bill of materials processing
USE AdventureWorks;
GO
CREATE PROC AssemblyComposition
           @AssemblyID INT
AS
  BEGIN
    WITH cteAssembly(AssemblyID,ComponentID,PerAssemblyQty,ComponentLevel)
 -- Root (anchor) part
        AS (SELECT b.ProductAssemblyID,
                    b.ComponentID,
                    b.PerAssemblyQty,
                    0 AS ComponentLevel
             FROM   Production.BillOfMaterials AS b
             WHERE  b.ProductAssemblyID = @AssemblyID
-- Recursive part - recursive CTE
             UNION ALL
             SELECT bom.ProductAssemblyID,
                    bom.ComponentID,
                    p.PerAssemblyQty,
                    ComponentLevel + 1
             FROM   Production.BillOfMaterials AS bom
                    INNER JOIN cteAssembly AS p
                      ON bom.ProductAssemblyID = p.ComponentID)
    SELECT   ASSEMBLY = p1.Name,
             a.ComponentID,
             Component = p2.Name,
             a.PerAssemblyQty,
             ComponentLevel
    FROM     Production.Product p1
             INNER JOIN cteAssembly AS a
               ON a.AssemblyID = p1.ProductID
             INNER JOIN Production.Product AS p2
               ON a.ComponentID = p2.ProductID
    ORDER BY ComponentLevel,
             ASSEMBLY,
             Component;
  END
 
GO
-- T-SQL TOP function & newid function for random results
SELECT TOP (5) ProductID, ProductName=Name, ListPrice
FROM Production.Product ORDER BY NEWID()
GO
/*
ProductID   ProductName                   ListPrice
999         Road-750 Black, 52            539.99
317         LL Crankarm                   0.00
867         Women's Mountain Shorts, S    69.99
930         HL Mountain Tire              35.00
820         HL Road Front Wheel           330.06
*/
 
-- Assembling HL Road Front Wheel
-- Execute stored procedure with recursive tree processing
EXEC AssemblyComposition   999
GO
/* Results
 

ASSEMBLY ComponentID Component PerAssemblyQty ComponentLevel
Road-750 Black, 52 952 Chain 2 0
Road-750 Black, 52 952 Chain 1 0
Road-750 Black, 52 948 Front Brakes 1 0
Road-750 Black, 52 945 Front Derailleur 1 0
Road-750 Black, 52 994 LL Bottom Bracket 1 0
Road-750 Black, 52 949 LL Crankset 1 0
Road-750 Black, 52 805 LL Headset 2 0
Road-750 Black, 52 805 LL Headset 1 0
Road-750 Black, 52 738 LL Road Frame - Black, 52 1 0
Road-750 Black, 52 818 LL Road Front Wheel 1 0
Road-750 Black, 52 811 LL Road Handlebars 1 0
Road-750 Black, 52 938 LL Road Pedal 1 0
Road-750 Black, 52 826 LL Road Rear Wheel 1 0
Road-750 Black, 52 517 LL Road Seat Assembly 1 0
Road-750 Black, 52 907 Rear Brakes 1 0
Road-750 Black, 52 894 Rear Derailleur 1 0
Front Derailleur 351 Front Derailleur Cage 1 1
Front Derailleur 352 Front Derailleur Linkage 1 1
LL Bottom Bracket 3 BB Ball Bearing 1 1
LL Bottom Bracket 525 LL Shell 1 1
LL Crankset 322 Chainring 1 1
LL Crankset 322 Chainring 1 1
LL Crankset 320 Chainring Bolts 1 1
LL Crankset 321 Chainring Nut 1 1
LL Crankset 332 Freewheel 1 1
LL Crankset 317 LL Crankarm 1 1
LL Headset 1 Adjustable Race 1 1
LL Headset 1 Adjustable Race 1 1
LL Headset 1 Adjustable Race 2 1
LL Headset 1 Adjustable Race 2 1
LL Headset 323 Crown Race 1 1
LL Headset 323 Crown Race 2 1
LL Headset 4 Headset Ball Bearings 1 1
LL Headset 4 Headset Ball Bearings 2 1
LL Headset 402 Keyed Washer 1 1
LL Headset 402 Keyed Washer 2 1
LL Headset 459 Lock Nut 19 2 1
LL Headset 459 Lock Nut 19 1 1
LL Headset 462 Lower Head Race 1 1
LL Headset 462 Lower Head Race 2 1
LL Road Frame - Black, 52 324 Chain Stays 1 1
LL Road Frame - Black, 52 325 Decal 1 1 1
LL Road Frame - Black, 52 326 Decal 2 1 1
LL Road Frame - Black, 52 327 Down Tube 1 1
LL Road Frame - Black, 52 327 Down Tube 1 1
LL Road Frame - Black, 52 399 Head Tube 1 1
LL Road Frame - Black, 52 802 LL Fork 1 1
LL Road Frame - Black, 52 492 Paint - Black 1 1
LL Road Frame - Black, 52 532 Seat Stays 1 1
LL Road Frame - Black, 52 533 Seat Tube 1 1
LL Road Frame - Black, 52 534 Top Tube 1 1
LL Road Front Wheel 400 LL Hub 1 1
LL Road Front Wheel 490 LL Nipple 1 1
LL Road Front Wheel 510 LL Road Rim 1 1
LL Road Front Wheel 931 LL Road Tire 1 1
LL Road Front Wheel 506 Reflector 1 1
LL Road Front Wheel 506 Reflector 1 1
LL Road Front Wheel 922 Road Tire Tube 1 1
LL Road Front Wheel 527 Spokes 1 1
LL Road Handlebars 398 Handlebar Tube 1 1
LL Road Handlebars 356 LL Grip Tape 1 1
LL Road Handlebars 329 Road End Caps 1 1
LL Road Handlebars 529 Stem 1 1
LL Road Rear Wheel 400 LL Hub 1 1
LL Road Rear Wheel 490 LL Nipple 1 1
LL Road Rear Wheel 510 LL Road Rim 1 1
LL Road Rear Wheel 510 LL Road Rim 1 1
LL Road Rear Wheel 931 LL Road Tire 1 1
LL Road Rear Wheel 931 LL Road Tire 1 1
LL Road Rear Wheel 506 Reflector 1 1
LL Road Rear Wheel 922 Road Tire Tube 1 1
LL Road Rear Wheel 527 Spokes 1 1
LL Road Seat Assembly 911 LL Road Seat/Saddle 1 1
LL Road Seat Assembly 911 LL Road Seat/Saddle 1 1
LL Road Seat Assembly 497 Pinch Bolt 1 1
LL Road Seat Assembly 528 Seat Lug 1 1
LL Road Seat Assembly 530 Seat Post 1 1
Rear Derailleur 355 Guide Pulley 1 1
Rear Derailleur 679 Rear Derailleur Cage 1 1
Rear Derailleur 535 Tension Pulley 1 1
BB Ball Bearing 2 Bearing Ball 1 2
BB Ball Bearing 505 Cone-Shaped Race 1 2
BB Ball Bearing 504 Cup-Shaped Race 1 2
BB Ball Bearing 461 Lock Ring 1 2
Chain Stays 486 Metal Sheet 5 1 2
Down Tube 483 Metal Sheet 3 1 2
Down Tube 483 Metal Sheet 3 1 2
Handlebar Tube 487 Metal Sheet 6 1 2
Head Tube 485 Metal Sheet 4 1 2
LL Fork 316 Blade 1 2
LL Fork 350 Fork Crown 1 2
LL Fork 331 Fork End 1 2
LL Fork 531 Steerer 1 2
LL Hub 525 LL Shell 1 2
LL Hub 525 LL Shell 1 2
LL Hub 523 LL Spindle/Axle 1 2
LL Hub 523 LL Spindle/Axle 1 2
Road End Caps 482 Metal Sheet 2 1 2
Seat Stays 484 Metal Sheet 7 1 2
Seat Stays 484 Metal Sheet 7 1 2
Seat Tube 478 Metal Bar 2 1 2
Stem 477 Metal Bar 1 1 2
Top Tube 482 Metal Sheet 2 1 2
Blade 486 Metal Sheet 5 1 3
Fork Crown 486 Metal Sheet 5 1 3
Fork End 482 Metal Sheet 2 1 3
Steerer 487 Metal Sheet 6 1 3

 

*/
 

------------

Exam Prep 70-461
Exam 70-461