得到一个奇怪的错误，SQL Server 查询使用`WITH` 子句 [英] Getting an odd error, SQL Server query using `WITH` clause

问题描述

以下查询:

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';

正在生成此错误:

消息 319，级别 15，状态 1，第 5 行关键字附近的语法不正确'和'.如果这种说法是普遍的表表达式，一个 xml 命名空间子句或更改跟踪上下文子句，前面的语句必须是以分号结尾.

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.

仅在执行时.管理工作室的sql语句没有错误/警告.

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

有什么想法吗?

推荐答案

总是使用 with 像 ;WITH 这样的语句，那么你永远不会得到这个错误.WITH 命令需要在它和任何以前的命令之间使用 ;，通过始终使用 ;WITH，您将永远不必记住这样做.

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.

请参阅部分中的WITH common_table_expression (Transact-SQL)创建和使用通用表表达式的指南:

当在声明中使用 CTE 时是批处理的一部分，语句在它后面必须跟一个分号.

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

这篇关于得到一个奇怪的错误，SQL Server 查询使用`WITH` 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！