40

Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure

WITH SomeClause1 AS
(
  SELECT ....
)
WITH SomeClause2 AS
(
  SELECT ....
)

But the error occurs

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

What are my options? Is there any splitter I don't know about?

Duncan
  • 1,758
  • 6
  • 21
  • 34

4 Answers4

74

Use a comma to separate CTEs

;WITH SomeClause1 AS
(
  SELECT ....
)
, SomeClause2 AS
(
  SELECT ....
)
gbn
  • 422,506
  • 82
  • 585
  • 676
  • same goes for the SQL Server 2008 MERGE statement, too - it **must** be terminated with a semicolon! – marc_s Sep 17 '09 at 15:05
  • gbn, you saved my hide. I was getting this error on a query component in Delphi, was stumped! Thanks again. And you Duncan. – Hein du Plessis Jun 10 '11 at 14:24
18

Forget about adding a ";" to the previous statement, like the error message says. Just get in the habit of always coding it like: ";WITH" and you'll be fine...

;WITH SomeClause1 AS
(
  SELECT ....
)

however, you must connect multiple CTEs with commas, but the ";WITH" always has a semicolon before it:

;WITH SomeClause1 AS
(
  SELECT ....
)
,SomeClause2 AS
(
  SELECT ....
)
KM.
  • 101,727
  • 34
  • 178
  • 212
2

Try with upgrading SQL Server Database COMPATIBILITY_LEVEL

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Reference Link :- https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver15

Bhadresh Patel
  • 1,671
  • 17
  • 18
1

Doesn't work for me.

In my case I'm using the CTE value within the RETURN clause of a table-valued user-defined function. If I wrap the RETURN clause in BEGIN-END I get the same error message, but a bare RETURN() clause works okay. I believe the error message is incorrect in this case.

This works:

CREATE FUNCTION [dbo].[ft_SplitStringOnChar]
      (
      @s varchar(8000),
      @sep char(1)
      )

RETURNS TABLE
AS

RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn AS TokenNumber,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS TokenString
    FROM Pieces
  )

GO  

This does not:

CREATE FUNCTION [dbo].[ft_SplitStringOnChar]
      (
      @s varchar(8000),
      @sep char(1)
      )

RETURNS TABLE
AS
BEGIN
;
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn AS TokenNumber,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS TokenString
    FROM Pieces
  )
END
GO
gbn
  • 422,506
  • 82
  • 585
  • 676
Cornan
  • 11
  • 1