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:

  1. Thanks for this article. It's just what I was searching for. I am always interested in this subject.

    PIC Scheme Singapore

    ReplyDelete
  2. A The way you shared your knowledge here is amazing!

    Picgrant Singapore 2014

    ReplyDelete