Invalid object name SQL Server CTE Error

How to avoid the Invalid object name SQL Server CTE Error ? Or how to execute multiple SELECT queries on the same CTE ? As a reminder, a CTE is a temporary object that only exists between its creation and the first time it’s used. It means it’s deleted right after the first query ran against the CTE.

Indeed when querying the second time a CTE, for Common Table Expression, you face this error thrown by SQL Server. How to avoid the Invalid object name SQL Server CTE Error ?

This error is faced when a select query uses more than one time a CTE : Invalid object name

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

In fact, only the first SELECT query works not the second one or next ones. Here’s a CTE example with various SELECT queries executed against it.

Only the first SELECT statement on the CTE works!

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

The solution

It’s impossible to execute many times a SELECT statement against the same CTE, so use a temporary table instead of a CTE!

To conclude about the workaround for this CTE error: temporary tables are  definitely the best way to keep results and query them many times. This query replacing the CTE 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

Note: Check the existence of the table and if necessary drop it before to avoid errors.

Another option is to create a real table and just reuse it, you can find a script here to avoid conversion error from xml to nvarchar.