Why is the use of ';' mandatory in the WITH clause?

Asked

Viewed 3,377 times

4

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

The Error is very succinct, but it gives an idea that the WITH requires the previous instruction to be terminated, but why?

Query that creates the problem
Change to ;WITH, does not generate error. 

DECLARE @pID INT = 1

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

Error message in SQL:

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 must be terminated with a semicolon.

Português

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

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

References
Using common table expressions
Using WITH AS command in Sql Server

sql sql-server characteristic-language syntax
  • 1

    The problem is that word WITH can be used as hint also and this confuses the compiler.

  • 1

    @Wow Sorack! That makes total sense! This complements Maniero’s response and justifies what he said about the compiler. Thank you!

1 answer

6


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

DECLARE @pID INT = 1;

No matter what comes next.

You might not even have one ; if the previous declaration does not require it, for example:

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

I put in the Github for future reference.

Taken from documentation.

Browser other questions tagged sql sql-server characteristic-language syntax

You are not signed in. Login or sign up in order to post.