你好,我想弄清楚为什么在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)
这是我的功能：
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
解决方法
尝试在前面投掷一个半个冒号：
;with results as
(
select parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
给this article一个阅读,以了解为什么你需要这样做. 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做这件事,但如果这样使得事情变得更容易,那就不会伤害任何东西.
