SQL 关键字“with”附近的语法不正确...上一条语句必须以分号结尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1439123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon
提问by Duncan
Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure
我使用 SQL Server 2005。我的存储过程中有 2 个 WITH 子句
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?
我有哪些选择？有没有我不知道的分离器？
回答by gbn
Use a comma to separate CTEs
使用逗号分隔 CTE
;WITH SomeClause1 AS
(
SELECT ....
)
, SomeClause2 AS
(
SELECT ....
)
回答by KM.
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 ....
)
回答by fritz
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/
http://itknowledgeexchange.techtarget.com/sql-server/using-xmlnamespaces-within-a-function/
回答by Cornan
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 子句包装在 BEGIN-END 中，我会收到相同的错误消息，但裸 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
