在关键字“with”附近的不正确语法必须以分号结尾

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

Im使用SQL Server 2005。存储过程中有2个子句

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.

在关键字“with”附近的语法错误。如果该语句是通用表表达式或xmlnamespaces子句，则必须以分号结束前面的语句。

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

我的选择是什么?有没有我不知道的拆分器?

4 个解决方案

#1

Use a comma to separate CTEs

使用逗号分隔cte

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

#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”，然后你就没事了……

;WITH SomeClause1 AS
(
  SELECT ....
)

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

但是，您必须用逗号连接多个cte，但是“;with”前面总是有分号:

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

#3

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

Mladen Prajdic把它作为“使用xmlnamespaces”的解决方案，效果很好。

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

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

在我的例子中，我在一个表值用户定义函数的RETURN子句中使用CTE值。如果我在开始端包装RETURN子句，我将得到相同的错误消息，但是一个bare RETURN()子句可以正常工作。我认为错误消息在这种情况下是不正确的。

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

#1