Getting an odd error, SQL Server query using `WITH` clause

Question

The following query:

WITH 
    CteProductLookup(ProductId, oid) 
    AS 
    (
        SELECT p.ProductID, p.oid
        FROM [dbo].[ME_CatalogProducts] p 
    )

SELECT 
    rel.Name as RelationshipName,
    pl.ProductId as FromProductId,
    pl2.ProductId as ToProductId
FROM 
    (
    [dbo].[ME_CatalogRelationships] rel 
    INNER JOIN CteProductLookup pl 
    ON pl.oid = rel.from_oid
    ) 
    INNER JOIN CteProductLookup pl2 
    ON pl2.oid = rel.to_oid
WHERE
    rel.Name = 'BundleItem' AND
    pl.ProductId = 'MX12345';

Is generating this error:

Msg 319, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

On execution only. There are no errors/warnings in the sql statement in the managment studio.

Any ideas?

Armenta

why even use a CTE here? couldn't you just join to the actual table [dbo].[ME_CatalogProducts] instead of the cte CteProductLookup, which doesn't really do anything? – Nihility

For Extension later, but you're right I don't need it in this example. – Armenta

It appears you can get this message by having more than one with statement. you only need one for the first, then use a comma and just give the name for the next one with columns in brackets ad infinitum. You don't need a comma after last one, and then you can just select it – Mandy

Answer 1

always use with statement like ;WITH then you'll never get this error. The WITH command required a ; between it and any previous command, by always using ;WITH you'll never have to remember to do this.

see WITH common_table_expression (Transact-SQL), from the section Guidelines for Creating and Using Common Table Expressions:

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

Nihility

SQL Server is moving towards requiring statements to be terminated by semicolons. SQL2012 requires them in many more places than just CTEs and Merge statements. This fix seems to be a hack for a bug in their implementation. – Arabela

Unless they are going to "fix" all their TSQL code, yes it is a "hack". Otherwise, this will make it work, especially if you are just maintaining some legacy code. It is a good thing to be aware of, so you can plan for your upgrade. There are some automated tools that will add semicolons for you. – Nihility

Answer 2

;WITH 
    CteProductLookup(ProductId, oid) 
    AS 
...

Tramontane

Answer 3

It should be legal to put a semicolon directly before the WITH keyword.

Successful

Answer 4

In some cases this also occurs if you have table hints and you have spaces between WITH clause and your hint, so best to type it like:

SELECT Column1 FROM Table1 t1 WITH(NOLOCK)
INNER JOIN Table2 t2 WITH(NOLOCK) ON t1.Column1 = t2.Column1

And not:

SELECT Column1 FROM Table1 t1 WITH (NOLOCK)
INNER JOIN Table2 t2 WITH (NOLOCK) ON t1.Column1 = t2.Column1

Avidity