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?

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.

Answer 2

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