You can use variables in CTEs.

Below there is a sample you can widely meet in Yukon T-SQL enhancements in SQL Server 2005 books.

Note that you should use ";" after any sql statement just running before CTE (or With keyword)

Declare @Id as int
Set @Id = 13;
With SampleCTE (Id, Unit, ReportstoId, TypeId)
As
(
Select Id, Unit, ReportstoId, TypeId From CompanyUsers Where Id = @Id
Union All
Select CompanyUsers.Id, CompanyUsers.Unit, CompanyUsers.ReportstoId, CompanyUsers.TypeId
From CompanyUsers
Inner Join SampleCTE On SampleCTE.Id = CompanyUsers.ReportstoId
)
Select TypeName, Unit From SampleCTE
Inner Join Types On Types.Id = SampleCTE.TypeId
Go

Eralper

htpp://www.kodyaz.com

Temporary tables are kept until you drop them or the session is closed. And you can use them repeatedly within the session.

But CTE is only used by the Select, Insert, Update or Delete statement that comes after the CTE.

You can see the temporary table created under the tempdb user tables section by using SQL Query Analyzer.

Hi,

I don't know the term "forward-referenced", sorry.

Today a new article of Srinivas is published under the link,

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

This article summarizes the recursive usage of common table expressions very well with a few good examples.

I think if we compare CTE and temp tables, temp tables are more flexable. But if do not need the table repeatedly in your codes, you can choose CTE.

Eralper

http://www.kodyaz.com

If I repeat the same derived table 2 or 3 times in my SQL select statement, the execution plan looks like it executes the same select code several times.  Do CTEs make the execution plan more efficient in such cases or is it merely a (very) nice shorthand?

Srinivas Sampath:

Very nice job with almost no extra words in explain the CTE.

Some thoughts:

1. It looks like CTE could replace table variable in most of the case, is this right?

2. How long the live of CTE will be lasted? If is created in procA and it at the end called procB, could the same CTE be used in procB as a ## global temp table?

Want to see more articles from you!

thanks

David

Hi David,

As far as I know you can only use a CTE just after you define it.

Eralper

http://www.kodyaz.com