15 July 2014

Parts in CTE

A Common Table Expression contains three core parts:
The CTE name (this is what follows the WITH keyword)
The column list (optional)
The query (appears within parentheses after the AS keyword)
The query using the CTE must be the first query appearing after the CTE.
How to use CTE in SQL Server?
Syntax of CTE:
With Parameter
With T(<col Name>, <col name1>, <Col name2>)  --Column names for Temporary table
AS
(
SELECT A.<Col name>, E.<col name1>, E.<col name2> from <table_name> A
INNER JOIN <table_name> E ON E.<col name> = A.<col name>
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.[col name]  > 50
ORDER BY T.<col name>

Without Parameter
WITH MyCTE AS
(SELECT c.[Col name]
FROM [table_name] pc
INNER JOIN [table_name] c ON c.[Col name] = pc.[Col name])
SELECT cte.[Col name], p.[Col name]
FROM [table_name] p
INNER JOIN [table_name].[Col name] ea ON ea.[Col name] = p.[Col name]
INNER JOIN MyCTE cte ON cte.[Col name] = p.[Col name]
INNER JOIN [table_name].PersonPhone ph ON ph.[Col name] = p.[Col name];


No comments: