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.
