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