Why is it mandatory to use the ';' in the WITH clause?

Navigation
3

I've always used WITH in my queries, however, I've never understood exactly why '; ' before the WITH clause is required.

The error is very succinct, but gives an idea that WITH requires the previous statement to be closed, but why?

If you change to ;WITH , it does not generate the error. 

DECLARE @pID INT = 1

WITH CTE_TABELA AS
    (SELECT * FROM TABELA) 
SELECT * FROM CTE_TABELA CTE
WHERE CTE.ID = @pID

SQL error message:

  

Msg 319 , Level 15, State 1, Line 4
    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 should be terminated with a semicolon.

English

  

Incorrect syntax near the 'WITH' keyword. If this statement is a common table expression, an xmlnamespaces clause, or a change tracking context clause, the previous statement should be terminated with a semicolon .

The same question applies also clauses:
 - WITH XMLNAMESPACES Home  - WITH CHANGE_TRACKING_CONTEXT

References
Using Expressions of common table
Using WITH WITH command on Sql Server

    
asked by anonymous 27.10.2017 / 14:11

1 answer

5

The problem is not with WITH , it is with the previous statement that it needs to be terminated. The compiler is not always able to correctly identify the location and gives a misleading message, but doing so works: 

DECLARE @pID INT = 1;

No matter what comes next.

You may not even have a ; before if the previous statement does not need it, eg 

USE AdventureWorks2012;  
GO  
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)  
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        1,  
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)  
    FROM dbo.MyEmployees AS e  
    WHERE e.ManagerID IS NULL  
    UNION ALL  
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +  
        e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        EmployeeLevel + 1,  
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' +   
                 LastName)  
    FROM dbo.MyEmployees AS e  
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID  
    )  
SELECT EmployeeID, Name, Title, EmployeeLevel  
FROM DirectReports   
ORDER BY Sort;  
GO

Retired from documentation .

    
27.10.2017 / 14:25
How to avoid user logout when closing browser with PHP? How to solve this error correctly?