CTE results are not stored anywhere.... they don't produce results.... a CTE is just a definition, just like a VIEW is just a definition. Think of a CTE as being a View that only lasts for the duration of the query.

Is CTE stored in tempdb?

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. ... This is also created in the tempdb database but not the memory.

What is CTE in stored procedure?

According to the CTE documentation, Common Table Expression is a temporary result set or a table in which we can do CREATE, UPDATE, DELETE but only within that scope. That is, if we create the CTE in a Stored Procedure, we can't use it in another Stored Procedure.