CTE (Common Table Expression)
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.
The syntax of the CTE is the following.
WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name
Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.
Reference: http://dotnetdeve.blogspot.com/2010/07/cte-common-table-expression.html