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.

Suppose we have created OrderHistory table in current database:

CREATE TABLE OrderHistory(

     OrderId BIGINT PRIMARY KEY,

     OrderKey UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())

)

If will execute following sql query in sql server:

WITH cteOrder

AS(

     SELECT 

          OrderId,

          LEFT(OrderKey,1) AS Initials

     FROM OrderHistory

), WITH cteSubOrder

AS(

     SELECT TOP (2) * FROM cteOrder ORDER BY Initials

)SELECT * FROM cteSubOrder

Or

WITH XMLNAMESPACES ('sql' AS Prefix)

WITH cteOrder

AS(

     SELECT 

          OrderId,

          LEFT(OrderKey,1) AS Initials

     FROM OrderHistory

)

SELECT

     OrderId AS 'Prefix:OrderId',

     Initials AS 'Prefix:OrderKey' 

FROM cteOrder FOR XML AUTO

Or

SELECT *
INTO #Temp WITH(ROWLOCK)     

FROM OrderHistory

We will get error message :

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.

Cause: It is due to syntax error in both sql queries.

Solution:

Correct syntax are:

WITH cteOrder

AS(

     SELECT 

          OrderId,

          LEFT(OrderKey,1) AS Initials

     FROM OrderHistory

), cteSubOrder

AS(

     SELECT TOP (2) * FROM cteOrder ORDER BY Initials

)SELECT * FROM cteSubOrder

Or

WITH XMLNAMESPACES ('sql' AS Prefix),

cteOrder

AS(

     SELECT 

          OrderId,

          LEFT(OrderKey,1) AS Initials

     FROM OrderHistory

)

SELECT

     OrderId AS 'Prefix:OrderId',

     Initials AS 'Prefix:OrderKey' 

FROM cteOrder FOR XML AUTO

Or

SELECT *
INTO #Temp     

FROM OrderHistory

Note: We cannot use table hints in INTO clause.