Looking at Math with Recursive CTE’s

Transcription of Video:

Let’s take a look at another recursive CTE. That’s not looking at tables, but it’s just looking at math. And here, we could create a recursive function that’s called sum of parts, and select from the sum of parts. Basically, we’re creating a function that selects from itself. And what it does is it says give me the current number, plus the sum of parts of that number minus one. So if we say sum of parts of 10, we should get the 55 that we asked for or the sum of parts of three, and we, we get six because three plus two plus one and six. But when we look at some of parts of something a little bit larger, oh gee, the nesting level on stored procedures, functions or triggers is 32. So we can only using recursion with functions go 32 levels deep on the recursion. Okay, so let’s take a look at how to do the same thing with a CTE and get around that limitation. First, we build the anchor query, we say select one as the count number, and one is the grand total. So the sum of parts of one is simply just one because one plus nothing is one, then what we do is we pop that into a CTE, wrap it with a CTE. And it’s still just the anchor query, just counting to one. Okay, not very exciting, because technically, all of us could have done that one in our head, we don’t need a database to do that.

But let’s take a look at the recursive query. For this, we’re going to say, give me the count number plus one, that’s our level we’ve gone to, and we’re looking at the grant. So this would be the count number would initially be one, and then it would be two, and then it’d be three as it goes through there. If this was at level three, the grand total would be the six from the previous level, plus the count number plus one, which would be three plus one would be four, and it would make it 10. For level four, we put all that into a CTE, we declare the CTE, we throw in the anchor query, we use the union all to connect it up as a recursive query, we put in the recursive parts. And then we select from it. Well, we ran into an error there, the maximum recursion of 100 has been exhausted before statement completion. Well, we did get further than we did with the recursive function. And you can see if we look here, at level 10, we’re getting 55. At level 11, we get 66, which is 55 plus 11, we go way down here at 100, we get 5050. And then at 101, we’ve gone beyond the max recursion level and things stop. So the way to get around this is we can add a where clause that will terminate the recursion at some level. So if we just wanted to look at up to level 10, on the sum of parts, we could use a where clause like this. And what that did, we’ll fix the layout here a little bit, so we can look at it is it said, only run the recursive query until the count number is at 10. So only run it 10 times. If we change this to 20. Run the CTE again, we get the sum of parts for 20 is 210. Okay, but what we were looking for in our initial math function was not a list of the sum of parts of everything up to that level, we were really looking for just the answer. So what is the sum of parts of 10? Well, in this case, we’re just gonna say run that whole query and give me the biggest number, up to level 10. And that’s the sum of parts. There we go, sum of parts is 55, when the recursion level is 10, or the count number is less than 10. Would if we wanted to go further, if we want to do the sum of parts of 150. Let’s try that same CTE, all I’ve done is changed that 10 to 150. Down here, we go and run that. And kaboom, we get an error, the statement has been terminated the maximum recursion of 100 has been exhausted before statement completion. Okay, the way we’re going to get around that is we’re going to put in this parameter called option Max recursion. And I’m just going to throw in 200 as a number I could have technically put in 151 has been slightly larger than what I’m looking for here. But I’m just gonna throw in 200. You could go with something bigger like 1000 It would work fine. And when we run that, we get the sum of parts for 150, which is 11,325.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!