Overall, I like what you did. The code snippets are small and manageable. The only downside to it is when it comes time to debug it.
What I've seen where I work is that the bug is found in the application (C#) and from debugging that you narrow it down to a single stored procedure (hopefully). Using your method, you then jump into the stored procedure to jump into a function to jump into 1 or more functions. SSMS (as far as I am aware) doesn't have nice native ability like C#'s "go to definition".
On the opposite spectrum, testing this is going to be a lot more simple and understanding the code is also a lot more simple. Reading a lengthy stored procedure (we have some 8000+ line stored procedures here at work) is VERY easy to misread things and miss potential bugs and unit testing an 8000+ line stored procedure is impossible. It also results in the code not being reused, or if it IS reused, it is copy-pasted to be reused which is not very efficient.
My only gripe with the code is the lack of comments. Having comments explaining what the code is doing and what the expected input and output are make it a lot easier to support and reuse. That being said, your code is short and sweet, so not positive that comments would help; it is just part of our coding standards. Much easier to have a rule of putting comments everywhere rather than have subjective rules like "comments only required for complex code". I've seen that before and what is complex to me may be simple to you. I know some developers really dislike the WITH syntax and would much rather use nested selects or store the results in temp tables or table variables. I am liking the function approach and I actually have a report (SSRS) that I am working on now that could benefit from this approach.
But after reading through the whole article, I think I am going to be more inclined to try to enforce your suggestions going forward here at work. The code becomes reusable which is always a HUGE plus and if/when a bug is found, fixing it will fix it everywhere the code function is used. Having copy-pasted code means you may (likely will) need to bug fix things in more than once place when a bug is found.