SQL 关键字“with”附近的语法不正确。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7032425/
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'.
提问by Kyle Rogers
Hello I'm trying to figure out why switching my compatability mode from 80 to 100 in MSSQL broke my function below?
您好，我想弄清楚为什么在 MSSQL 中将我的兼容性模式从 80 切换到 100 会破坏我的以下功能？
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on
Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Here is my function:
这是我的功能：
GO
ALTER FUNCTION [dbo].[GetRoot]
(
@Param1 int
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnValue varchar(50)
with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
select @ReturnValue = net_ou.displayname
from NET_OU RIGHT OUTER JOIN
results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid
RETURN @ReturnValue
END
回答by Abe Miessler
Try throwing a semi colon in front of the with:
尝试在 with 前面扔一个分号：
;with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
Give this articlea read to understand why you need to do that. Snipit:
给这篇文章读明白，为什么你需要做到这一点。截图：
However, if the CTE is not the first statement in the batch, you must precede the WITH keyword with a semicolon. As a best practice, I prefer to prefix all of my CTEs with a semicolon—I find this consistent approach easier than having to remember whether I need a semicolon or not.
但是，如果 CTE 不是批处理中的第一条语句，则必须在 WITH 关键字之前使用分号。作为最佳实践，我更喜欢在我的所有 CTE 前面加上分号——我发现这种一致的方法比必须记住是否需要分号更容易。
Personally, I don't do it for everyCTE, but if that makes things easier for you it won't hurt anything.
就我个人而言，我不会为每个CTE都这样做，但如果这对您来说更容易，它不会有任何伤害。
回答by JNK
Add a semicolon before WITH:
前加分号WITH：
;with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
select @ReturnValue = net_ou.displayname
from NET_OU RIGHT OUTER JOIN
results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid
RETURN @ReturnValue
END
CTE declarations need to be the first command in the batch.
CTE 声明需要是批处理中的第一个命令。
回答by Joe Harris
I would suggest that you adopt the practice of ending all statements with a semicolon. This is part of the ANSI standard and will help you when need to work on another database. SQL Server are moving towards this in any case. Many more commands require semicolons now in SQL Server 2012.
我建议您采用以分号结束所有语句的做法。这是 ANSI 标准的一部分，将在需要处理另一个数据库时为您提供帮助。无论如何，SQL Server 都在朝着这个方向发展。现在在 SQL Server 2012 中，更多的命令需要分号。
E.g.
例如
ALTER FUNCTION [dbo].[GetRoot]
(@Param1 int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(50)
;
WITH cteResults
AS (SELECT parentouid
,net_ouid
FROM net_ou
WHERE net_ouid=@Param1
UNION ALL
SELECT t2.parentouid,t2.net_ouid
FROM net_ou t2
INNER JOIN results t1
ON t1.parentouid = t2.net_ouid
WHERE t2.parentouid <> t1.net_ouid )
SELECT @ReturnValue = net_ou.displayname
FROM net_ou
RIGHT JOIN cteResults
ON net_ou.net_ouid = results.ParentouID
WHERE results.parentouid=results.net_ouid
;
RETURN @ReturnValue
;
END
;
GO
As an added bonus it makes you queries a crap load easier to read. ;-)
作为一个额外的好处，它使您查询垃圾负载更容易阅读。;-)
