Incorrect syntax near the keyword 'with'…p

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

Mladen Prajdic suggested this as a solution for "with xmlnamespaces", works great.

http://itknowledgeexchange.techtarget.com/sql-server/using-xmlnamespaces-within-a-function/

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

Use a comma to separate CTEs

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

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