scope of a cte - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

scope of a cte

Author

Topic

dev2dev
Starting Member

48 Posts

Posted - 2007-12-28 : 04:49:53

Hi,

I was under impression that CTE is a reusable query that is defined once and used in many places withing view or procedure but seems like its wrong

once i define cte, I can reference it only in once sql, it is invisible in subsequent sql

see this example



declare @n int
set @n = 5;

      WITH CTE1 (N) AS(
    SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N
 UNION ALL
    SELECT N+1 N
      FROM CTE1
     WHERE N < @N
)

select * from cte1 -- this works fine
select * from cte1 -- this gives error Invalid object name 'cte1'

so now can i say cte is not re-usable query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-28 : 05:20:30

BOL on CTE http://msdn2.microsoft.com/en-us/library/ms190766.aspx
state

quote:
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

KH
[spoiler]Time is always against us[/spoiler]

dev2dev
Starting Member

48 Posts

Posted - 2007-12-28 : 05:38:10

quote:

result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement

i might have not understood that line well
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 06:11:31

quote:
Originally posted by dev2dev

quote:

result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement

i might have not understood that line well
thanks

You can refer that in a single statement only
But you can make use of it for number of times in a join, union, etc

declare @n int
set @n = 5;

WITH CTE1 (N) AS(
SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N
UNION ALL
SELECT N+1 N
FROM CTE1
WHERE N < @N
)

select c1.* from cte1 c1 cross join cte1 c2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources