Parts in CTE

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.

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>

Use CTE in SQL Server

Syntax of CTE: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];