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)

CTE

Author

Topic

renu
Starting Member

47 Posts

Posted - 2007-10-24 : 00:49:10

hi all,

I have a query in CTE. Once the CTE was created us itpossible to use anywhere in the SP like temp table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 01:49:16

No, it is not.
You can however only use it IMMEDIATELEY after creating the CTE.

Look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89365

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 02:46:46

Also you can use it immediately for only one time


WITH t as
(
	select 1 as n union all select 10
)
select n from t
select n from t

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 02:56:17

Mmmm.. Not quite right.
You can only reference the CTE with one statement, but this one statement can reference the CTE one, twice or more times.

SELECT * FROM cte where Col1 = 2
UNION ALL
SELECT * FROM cte where Col2 = 1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 04:36:01

quote:
Originally posted by Peso

Mmmm.. Not quite right.
You can only reference the CTE with one statement, but this one statement can reference the CTE one, twice or more times.

SELECT * FROM cte where Col1 = 2
UNION ALL
SELECT * FROM cte where Col2 = 1

E 12°55'05.25"
N 56°04'39.16"

Yes it is. Thats why my example

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources