在关键字“with”附近的语法错误。

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

3 个解决方案

#1

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 article a read to understand why you need to do that. Snipit:

请阅读本文，了解为什么需要这样做。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 every CTE, but if that makes things easier for you it won't hurt anything.

就我个人而言，我不是对每一个CTE都这么做，但是如果这样做对你来说更容易的话，也不会对你造成任何伤害。

#2

Add a semicolon before 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声明必须是批处理中的第一个命令。

#3

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. ;-)

作为额外的好处，它使您查询一个垃圾负载更容易阅读。:-)

#1