Incorrect syntax near the keyword 'with'.

Question

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)

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

Abe Miessler · Accepted Answer · 2011-08-11T20:38:17.727

42

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:

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.

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

edited Aug 11 '11 at 20:38

answered Aug 11 '11 at 20:31

Abe Miessler

82,532
99
305
486

No problem. Take a look at my update to understand why that fixed it. – Abe Miessler Aug 11 '11 at 20:37
3

What a difference 30 seconds makes, apparently! :) +1 regardless. – JNK Aug 11 '11 at 20:39
+1, If only you hadn't entered the whole query all this juicy rep could have been yours! – Abe Miessler Aug 11 '11 at 20:41
1

LOL so true! Also may want to point out to OP it's the declaration, so if you declare multiple `CTE`s only the `WITH` should have the semicolon (I've seen people confused on this before). – JNK Aug 11 '11 at 20:42

score 6 · Answer 2 · answered Aug 11 '11 at 20:32

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.

score 0 · Answer 3 · answered Aug 29 '12 at 15:02

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.

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

Incorrect syntax near the keyword 'with'.

3 Answers3

Linked

Related