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.
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.
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.