Sep 5, 2013

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.

2 comments:

Sherry Beck said...

It’s amazing in support of me to truly have a web site that is valuable meant for my knowledge.

Lucy Fernandez said...

The client remains to implement the planning, UX agency project management. Maintained meticulous billing practices and remained within budget