Recursion with T – SQL

Hey there, long time no see!

I know I’ve been absent for a while, but you know… the holidays, the food, Christmas, new year… all that, plus now that with all the craziness happening with the COVID-19, it’s time to get back to learning.

So, let me tell you, a few months ago I had to write a query to get some data, but the data was not very easy to handle, so it was a challenge. A big one.

Let me elaborate:

I needed to get an ID from one table, and then query another table using that ID in the WHERE clause, the results would be more IDs, and then query those IDs and so on, until there were no more rows returned (you see where I’m going?)

I couldn’t use a cursor or a WHILE loop because I didn’t know when I was going to stop getting rows returned. I knew there were several “levels”, but not all the data returned would have the same number, so I was not able to use this approach.

USING CTEs

A Common Table Expression or CTE is very similar to a derived table, both of them are visible only to the statement that defined them, and both involve an inner query, the name you assign to the query and the columns, and the outer query.

How you write a CTE is different from a derived table, though. If you remember, in a derived table, the inner query appears in the FROM clause of the outer query, but in Common Table Expressions, first you have to give it a name, then specify the inner query and finally, the outer query, the syntax is like this:

I’m not going to talk in depth about Common Table Expressions, since there is a previous post already. What I do want to talk about now, is about how to use recursive CTEs. If you don’t know what recursion is, click here to find out more.

WHAT ARE RECURSIVE CTEs?

Recursive CTEs are just another type of them. The difference between them is that the former has two or more inner queries, separated by a UNION ALL operator.

One of the queries is called the anchor member, which is a query that returns valid data, and we also have the recursive member, which has a reference to the CTE name. The latter is called repeatedly until it returns an empty result set.

The outer query represents the union of both results, the anchor and the recursive part of the CTE.

Let me show you how recursion works in T -SQL with an example. I didn’t know any of this until I joined my current job, what I was trying to achieve is called a “Bill Of Materials”, or BOM for short.

For this example, I’m going to use AdventureWorks database. If you don’t have it, you can download a copy from here.

First, we start with a “base” query to get the information for the top level parts.

Now, we’re going to use the UNION ALL operator and wrap everything in a CTE that’s going to call itself to generate all the sub components.

Look how this query works. The first part (or the anchor) will get all the top level products, the second query, is the recursive member which will be generating all the “children” sub products used to make the final product.

I’m not an expert on this and it could probably be wrong, but we can always modify the query as per the business needs. In my case, this approach worked just fine except for the presentation, which I will be talking about in the next post.

Recursive queries are very useful when you need to create an organizational chart, and that’s how I had used them before. Now that I know what a Bill Of Materials is, I can use recursion to get the data needed.

This is not rocket science, however, a bad constructed recursive CTE can cause an infinite loop. To prevent this kind of behavior, we have a hint called MAXRECURSION which can limit the levels of recursion in your CTE. The value goes from 0 to 32,767 and can be used in the OPTION clause of INSERT, UPDATE, DELETE or SELECT statements.

Let’s see an example. In the query below, I’m creating an infinite loop. Look at how I’m calling columns from the CTE in the recursive member of the query.

And because it reached the maximum level of recursion, an error message will be displayed.

This is where we can use the MAXRECURSION hint.

You can see part of the query where I included OPTION(MAXRECURSION 2) to only allow for two levels of recursion.

So, this is the end for today, I hope you liked the post.

Also, remember that you can find the code for this and other posts in the link below:

https://github.com/learningsql/sql-code

If you have any questions or comments, shoot me an email, leave a message or tweet me here.

Don’t forget to like and share. See you soon. Cheers!