SQL Server Performance Forum – Threads Archive
Scope of CTE
When I run this code, I am getting the error Invalid object name aDoes it mean that the derived table cant be used successively? with a
(
dates
) as (select getdate() as dates) select dates from a
select dateadd(month,10,dates) from a
Madhivanan Failing to plan is Planning to fail
The query using the CTE must be the first query appearing after the CTE.http://sequelserver.blogspot.com/2007/05/new-features-common-table-expressions.html fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks. It means that if the same CTE is used in more than one places, should it be derived again and again?
I thought the scope would be as similar to that of temp table or table variable Madhivanan Failing to plan is Planning to fail
That was my assumption as well. Like with the MERGE syntax in Katmai – it looks really nice, but you don’t really write less code than before. If you have to reuse, you might as well feed the results into a table variable.
]]>