WITH common_table_expression (Transact-SQL)

WITH common_table_expression (Transact-SQL)

 

Specifies a temporary named result set, known as a common table expression (CTE).

This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

A common table expression can include references to itself.

This is referred to as a recursive common table expression.

 

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )  

 

 

Remarks

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.

A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

 

 

Error

Incorrect syntax near the keyword 'with'.

If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

https://stackoverflow.com/questions/1439123/incorrect-syntax-near-the-keyword-with-previous-statement-must-be-terminated

 

 

 

When to use with clause in sql

The WITH keyword is used to create a temporary named result set. These are called Common Table Expressions.

A very basic, self-explanatory example:

WITH Administrators (Name, Surname)
AS
(
    SELECT Name, Surname FROM Users WHERE AccessRights = 'Admin'
)
SELECT * FROM Administrators 

---

For further reading and more examples, I suggest starting out with the following MSDN article:

• Common Table Expressions by John Papa