关键字“with"附近的语法不正确. [英] Incorrect syntax near the keyword 'with'.

问题描述

您好，我想弄清楚为什么在 MSSQL 中将我的兼容性模式从 80 切换到 100 会破坏我的以下功能?

Hello I'm trying to figure out why switching my compatability mode from 80 to 100 in MSSQL broke my function below?

    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 前面扔一个分号:

Try throwing a semi colon in front of the 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:

但是，如果 CTE 不是批处理中的第一个语句，则必须在 WITH 关键字前加一个分号.作为最佳实践，我更喜欢在我所有的 CTE 前加一个分号——我发现这个一致的方法比必须记住我是否需要一个更容易分号与否.

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 都这样做，但如果这对您来说更轻松，则不会有任何伤害.

Personally, I don't do it for every CTE, but if that makes things easier for you it won't hurt anything.

这篇关于关键字“with"附近的语法不正确.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！