了解CTE分号放置 [英] Understanding CTE Semicolon Placement

问题描述

当我在SQL Server中运行此CTE时，它说声明语句表示语法不正确。

When I run this CTE in SQL Server it says the syntax is incorrect by the declare statement.

;WITH cte as
(
SELECT tblKBFolders.FolderID
from tblKBFolders
where FolderID = @FolderID

UNION ALL

SELECT tblKBFolders.FolderID
FROM tblKBFolders  
INNER JOIN cte
ON cte.FolderID = tblKBFolders.ParentFolderID
)

declare @tblQueryFolders as table (FolderID uniqueidentifier)
insert into @tblQueryFolders
SELECT FolderID From cte;

但是如果我将 declare 移到CTE之前，它将

But if I move the declare to before the CTE, it runs just fine.

declare @tblQueryFolders as table (FolderID uniqueidentifier)

;WITH cte as
(
SELECT tblKBFolders.FolderID
from tblKBFolders
where FolderID = @FolderID

UNION ALL

SELECT tblKBFolders.FolderID
FROM tblKBFolders  
INNER JOIN cte
ON cte.FolderID = tblKBFolders.ParentFolderID
)

insert into @tblQueryFolders
SELECT FolderID From cte;

为什么？

推荐答案

您所要求的答案已经在注释中给出：这与分号的位置无关。

The answer you ask for was given in a comment already: This has nothing to do with the semicolon's placement.

重要：CTE的有不能在没有结尾分号的语句后立即跟随。有很多陈述，其中 WITH 子句会在语句末尾添加一些内容（查询提示， WITH 在 OPENJSON 等之后）。引擎将不得不猜测，是否 WITH 是在CTE开始之前添加到语句中。这就是原因，为什么我们经常看到

Important: The CTE's WITH cannot follow right after a statement without an ending semicolon. There are many statments, where a WITH-clause would add something to the end of the statement (query hints, the WITH after OPENJSON etc.). The engine would have to guess, whether this WITH adds to the statment before or if it is a CTE's start. That's the reason, why we often see

;WITH cte AS (...)

这实际上是分号的错误用法。人们把它放在那里，只是不要忘记它。无论如何，将T-SQL语句始终以分号结束总是被视为更好的样式和最佳实践（并且不使用; WITH ，因为它实际上添加了一个空语句。

That's actually the wrong usage of a semicolon. People put it there, just not to forget about it. Anyway it is seen as better style and best practice to end T-SQL statements always with a semicolon (and do not use ;WITH, as it adds an empty statement actually).

CTE只不过是语法糖。将CTE的代码放在 FROM（SELECT ...）AS SomeAlias 内大致相同。在大多数情况下，这将导致相同的执行计划。在需要在多个地方编写与SomeAlias 相同的 FROM（SELECT）的情况下，它可以提供帮助。通常，它使事情更容易阅读和理解。但是，无论如何，它与临时表或表变量不具有可比性。引擎会将其视为内联代码，您可以在同一条语句中独占使用它。

A CTE is not much more than syntactical sugar. Putting the CTE's code within a FROM(SELECT ...) AS SomeAlias would be roughly the same. In most cases this would lead to the same execution plan. It helps in cases, where you'd have to write the same FROM(SELECT ) AS SomeAlias in multiple places. And - in general - it makes things easier to read and understand. But it is not - by any means - comparable to a temp table or a table variable. The engine will treat it as inline code and you can use it in the same statement exclusively.

所以这是相同的：

WITH SomeCTE AS(...some query here...)
SELECT SomeCTE.* FROM SomeCTE;


SELECT SomeAlias.* 
FROM (...some query here...) AS SomeAlias;

您的示例似乎将CTE视为临时表定义您可以在以下语句中使用。

Your example looks like you think of the CTE as kind of a temp table definition, which you can use in the following statements. But this is not correct.

在CTE之后，引擎需要另一个CTE或最终声明，例如 SELECT 或 UPDATE 。

After the CTE the engine expects another CTE or a final statement like SELECT or UPDATE.

WITH SomeCTE AS(...some query here...)
SELECT * FROM SomeCTE;

或

WITH SomeCTE AS( ...query... )
    ,AnotherCTE AS ( ...query... )
SELECT * FROM AnotherCTE;

...或其他带有 WITH 子句的内容：

...or another content added with the WITH clause:

WITH XMLNAMESPACES( ...namespace declarations...)
    ,SomeCTE AS( ...query... )
SELECT * FROM SomeCTE;

所有这些示例都是一条语句。

在中间放一个 DECLARE @Something 会破坏这个概念。

Putting a DECLARE @Something in the middle, would break this concept.

这篇关于了解CTE分号放置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！