Question: I found myself in need to use two CTE in one single SQL Query, however, I wasn't successful because the query threw an error.
Please Login to see the rest of the answer
However, the limit of using a CTE in #SQL Query is that the CTE can only be accessed once by an execting transaction or line of SQL code. This creates all sorts of problems, of course, this is based on how you intend to use it.
See the example below:
-- Example Create proc OneCTEandTempTable as with cte as ( --Your Code ) --Select into a temp table from cte, this is make sure you can access the data several times --within the session select * into #OneCTETempTable from cte --Then query the heck out of the temp table select top 100 * from #OneCTETempTable
In conclusion, go with the Temp table or you could create a #CTE then query the data from the CTE into the Temp Table then access the temp table many times within the executing session.
I hope this helps you in deciding whether you want to use two CTE in one Single SQL Query, it does leave us a comment below.