Call a SQL Server CTE multiple times and reuse the data

How to run multiple SELECT queries on the same SQL Server CTE?

Is it possible to execute several selection queries on a CTE, i.e. to call a SQL Server CTE multiple times in the same stored procedure for example? First of all, a CTE (Common Table Expression) allows you to reuse the result of a selection query. It is a temporary dataset, available only during the script. The CTE is widely used in particular to create recursive queries.

Can a SQL Server CTE be called multiple times?

It is true that on the second SELECT query on a CTE this error is returned by SQL Server:

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 9
Invalid object name ‘MyCTE’.

Indeed, it seems impossible to reuse a CTE several times. Otherwise, the error is displayed, Invalid object name with a SQL Server CTE. So, no CTE’s are not made to be called multiple times. The first SELECT query on the CTE works but not the following ones. Here is an example of a CTE with SELECT queries:

WITH MyCTE ([Yesterday], [Today], [Tomorrow]) AS
(
  SELECT 
    getdate()-1  as [Yesterday], 
    getdate()    as [Today], 
    getdate()+1  as [Tomorrow]
)

SELECT [Yesterday] FROM MyCTE;

SELECT [Today]     FROM MyCTE;

SELECT [Tomorrow]  FROM MyCTE;

In SQL Server, a CTE for Common Table Expression is a temporary object that exists only after it is created. It is then deleted after the first query executed on it. Therefore, a temporary table must be used to preserve the results.

Using a temporary table instead of the CTE

This query which replaces the ETC with a temporary table works perfectly.

IF OBJECT_ID('tempdb..#Temp_Table') IS NOT NULL
   DROP TABLE #Temp_Table;

SELECT 
  getdate()-1  as [Yesterday], 
  getdate()    as [Today], 
  getdate()+1  as [Tomorrow]
INTO      #Temp_Table;

SELECT [Yesterday] FROM #Temp_Table;
SELECT [Today]     FROM #Temp_Table;
SELECT [Tomorrow]  FROM #Temp_Table;

Also, be sure to check if the table exists and delete it first to avoid errors. Finally, here is a tutorial on another SQL Server topic, to learn how to transform rows into columns from a SQL Server table. You need to use the SQL Server PIVOT query.

Finally, this tutorial explains a method to call a SQL Server CTE multiple times and get around the problem.