SQL with AS Usage

A common table expression (CTE) is provided in Server 2005, using a CTE to make the SQL statement maintainable, while the CTE is much more efficient than a table variable.

Here is the syntax for the CTE:
[With <common_table_expression> [, N]]
<common_table_expression>::=
Expression_name [(column_name [, N])]
As
(cte_query_definition)

Now using the CTE to solve the above problem, the SQL statement is as follows:

With
CR as
(
Select Countryregioncode from person. CountryRegion where Name like ' c% '
)

SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from CR)

Where CR is a common table expression that is similar to a table variable in use, except that SQL Server 2005 differs in how common table expressions are handled.
The following points should be noted when using a CTE:
1. The CTE must be followed directly with the SQL statement that uses the CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. The CTE will not work correctly as in the following SQL statement:

With
CR as
(
Select Countryregioncode from person. CountryRegion where Name like ' c% '
)
SELECT * FROM person. CountryRegion--This SQL statement should be removed
--The SQL statement using the CTE should be immediately behind the relevant CTE--
SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from CR)

2. The CTE can also be followed by other CTE, but only one with, the middle of multiple CTE separated by commas (,), as shown in the following SQL statement:
With
Cte1 as
(
SELECT * FROM table1 where name like ' abc% '
),
Cte2 as
(
SELECT * from table2 where ID > 20
),
Cte3 as
(
SELECT * FROM Table3 where price < 100
)
Select A.* from Cte1 A, cte2 B, cte3 c where a.id = b.id and a.id = C.id

3. If the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the data table or view, as shown in the following SQL statement:
--table1 is a table that actually exists
With
Table1 AS
(
SELECT * from persons where age < 30
)
SELECT * FROM table1-uses a common table expression named table1
SELECT * FROM table1-using a data table named Table1

4. The CTE can refer to itself, or it can refer to a pre-defined CTE in the same with clause. Forward references are not allowed.

5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) ORDER by (unless the TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE

6. If the CTE is used in a statement that is part of a batch, then the statement before it must end with a semicolon, as shown in the following SQL:
declare @s nvarchar (3)
Set @s = ' c% '
; --Must add a semicolon
With
T_tree as
(
Select Countryregioncode from person. CountryRegion where Name like @s
)
SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from T_tree)

7. The CTE can also make recursive calls in addition to simplifying nested SQL statements