CTE (Common Table Expressions)

A common table expression (CTE) is a temporary named result set that can be referred to by a defining statement.

; WITH ()
AS
(

)

If the WITH clause for a CTE is not the first statement in the batch, you should delimit it from the preceding statement by placing a semicolon (;) in front of it.

Otherwise some time it will produce an error.

Msg 319, Level 15, State 1, Line 228
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

;WITH cte AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

--If we are having only CTE and no other statements
--it will produce an error.

--Msg 102, Level 15, State 1, Line 29
--Incorrect syntax near ')'.

;WITH cte AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

--SELECT * FROM City1
--SELECT * FROM cte

--A Common Table Expression must be called immediately after stating it.
--If not it will fail

;WITH cte (StationID, Station) AS
(

SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

SELECT * FROM cte

--All columns in the CTE must be in the Column List.
--Otherwise it will produce the error

--Msg 8158, Level 16, State 1, Line 1
--'cte' has more columns than were specified in the column list.

;WITH cte (A, B, C, D, E) AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

SELECT * FROM cte

--But we can give new name for each column in CTE in Column List

Recurisve CTE
When a CTE refers to itself, it is considered to be recursive.
This is mainly used when hierarchical data is stored in the table.

; WITH cteName (column list(optional)) AS
(
-- Anchor Query or Anchor Member(AM)
UNION ALL --(This is the only thing allowed)
-- Recursive Query or Recursive Member (RM).
)

Defined number of loops

Default MAXRECURSION is 1000.

SELECT * FROM cteName OPTION (MAXRECURSION 4)

MAXRECURSION OPTION allowed maximum is 32767.

Reference Link

http://jonathanparker.com.au/

http://www.databasejournal.com/

http://www.mssqltips.com/

http://omnibuzz-sql.blogspot.com

Search for the below text in the below link and it is explained well.

Recursive Queries and Common Table Expressions