Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon.

Question

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?

Answer 1

Use a comma to separate CTEs

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

Answer 2

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 ....
)

Answer 3

thanks it was very helpfull

Answer 4

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

Answer 5

This worked for me.