The SQL Inception Query (queue the “BRRRAAAAWWWW!”) is otherwise known as a Recursive CTE (Yawn.)
If you’re job is anything like mine, then you’ve probably had to create reports and stored procedures for databases you had no part in developing. Most of the time its no big deal. Certainly companies like Microsoft, with big developing budgets and programmers from MIT know how to design a database that doesnt require a PhD to manipulate, right?
Well, sometimes you are not so lucky. Enter this ProductStructure table, which I can only imagine was designed by an infant or someone who has completely given up on their career as a database administrator.
| ParentPart | ComponentPart | QtyPerUnit |
| A101 | B102 | 1 |
| B102 | X100 | 2 |
| B102 | X200 | 2 |
| B102 | BX300 | 2 |
| A101 | C103 | 1 |
| D105 | Z106 | 3 |
As you can see, there isnt a whole lot to it. The whole columns of data meant to tell me what components make up a part. It works well if I’m interested in finding out what components make up B102. But what about A101? It has B102 as a component and B102 is made up of 3 components. Oy vey!
Thankfully, the SQL gods have provided us with Recursive CTE’s, which is without a doubt my #1 favorite term to throw down at SQL meetups to sound like I’m the smartest guy there. (Take that “Guy who is always talking about CONCAT()”!)
Using recursive CTE’s we can run a multi tier query on this table to get all the components of a part, as well as all the components of those components, etc etc, so on a so forth. Hmmm, sort of reminds me of that Xzibit meme:
Anywho, check it out!
USE MyDatabase GO DECLARE @TheMainPart char(30) = 'A101'; WITH PartList (ParentPart, ComponentPart, QtyPerUnit) as ( ---define the anchor or main part in your query SELECT e.ParentPart, e.ComponentPart, e.QtyPerUnit FROM dbo.ProductStructure AS e WHERE e.ParentPart = @TheMainPart UNION ALL ---define recursive SELECT e.ParentPart, e.ComponentPart, e.QtyPerUnit FROM dbo.ProductStructure AS e INNER JOIN PartList AS d ON e.ParentPart = d.ComponentPart ) SELECT * FROM BOM;
Using that query, our return will exclude anything in the table that is not a component or a component of a component for Part A101.
| ParentPart | ComponentPart | QtyPerUnit |
| A101 | B102 | 1 |
| B102 | X100 | 2 |
| B102 | X200 | 2 |
| B102 | BX300 | 2 |
| A101 | C103 | 1 |
Not a big deal when my example includes a total of 6 parts, (we really just queried for everything except D105) but very helpful when your database consists of thousands parts.
To find out more about Recursive CTE’s you can check out this MSDN Article or check out this blog post, which give an example using the AdventureWorks database.
