A CTE can be called once in my SP

Question

see the code first. i am trying to learn the usage of CTE

with Test_cte as
(select * from testtable)
select * from Test_cte

--select * from Test_cte

When the above code execute it worked fine but the moment uncomment the last line then sql server throwing error called Invalid object name 'Test_cte'

what does it means....a CTE can be called once. if yes then it would be use less.

please share some knowledge about CTE and its usage. thanks

Answer 1

Hi @Sudip Bhatt ,

In SQL, a CTE can only be used/referenced in the (one) statement, where it is defined. And we know where statements end by using statement terminators (;).

SQL Server is forgiving and allows developers not to put these terminators (except for special cases where it does complain) but it's really good practice (and Microsoft recommends it) to use them after every statement. If you had them placed, it would be obvious that your code parses as 2 statements:

--- 1st statement starts ---  
with Test_cte as  
(select * from testtable)  
Select * from Test_cte ;    -- and ends here  
  
--- 2nd statement starts ---  
Select * from Test_cte ;    -- and ends here  

So your second statement should not actually work at all, and will return the error:

Invalid object name 'Test_cte'.  

As soon as the statement where the CTE is made ends, you lose the ability to reference it again.

However, you can run the two selects with a UNION/UNION ALL:

with Test_cte as  
(select * from testtable)  
  
select * from Test_cte  
union all  
select * from Test_cte  

Or you could use temp table or table variable instead if you want to use it twice.

You could also refer What are the advantages/disadvantages of using a CTE? for more details.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

if yes then it would be use less.

Yes, you can use a CTE just once after declaration.
A CTE is just a different way of writing queries, nothing else and no magic.

If you want to resume a query result, then use a temporary table or table variable to store the query result.