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?
This question is related to
sql-server
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.
It should be legal to put a semicolon directly before the WITH keyword.
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
;WITH
CteProductLookup(ProductId, oid)
AS
...
Source: Stackoverflow.com