Tree Queries in SQL Server with Common Table Expressions

My Friend and Fellow MVP in SQL Server, Jacob Sebastian is conducting a very good contest on TSQL. Every week he and his team are posting a very good challenge to write a SQL query to solve a problem. You can also find his interview @ 60 seconds with Jacob. The challenge is “about identifying all the employees directly or indirectly reporting to a given manager.” This looks so simple by the statement but not at all. You can write a query with Recurssive Common Table Expressions (CTE) to resolve this, but you should not pass or hard code any values inside the CTE. This is what makes it a bit complex. That is you can’t control the output that is coming from CTEs.