A CTE can be called once in my SP

Question

A CTE can be called once in my SP

Sudip Bhatt 2,281

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

Accepted answer

1 additional answer

Your answer

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.

Sudip Bhatt 2,281 Reputation points

2020-09-10T08:44:59.68+00:00

The way you explain it means CTE can be used only once in my SP. so i can not refer the same CTE repeatedly in my SP. am i right?
thanks
MelissaMa-MSFT 24,221 Reputation points

2020-09-10T08:46:55.633+00:00

Hi @Sudip Bhatt ,

Yes, you are right.

You could use temp table or table variable instead if you want to refer the same result set repeatedly.

Best regards
Melissa

Answer 2

Olaf Helper 47,441

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.

Share via

A CTE can be called once in my SP

1 additional answer

Your answer