How does the "With" keyword work in SQL?

Question

So many times seen with and, so many times SQL Server ask that with has ; before it

How does ;with ... work??

;with coords(...) as (
SELECT * ...
)

Why must have ; before it?

Solution 1

The semicolon is used in SQL to end a query. Putting it before a query like that is just to make sure that the database understands that any previous query has ended.

Originally it was required after each query as they were entered line by line, so the database had to know when to run the query. When the entire query is sent in a single string, you only need semicolons in the case where the SQL syntax is not enough to determine where a query ends. As the with keyword has different uses a semicolon is sometimes needed before it to make sure that it's not part of the previous query.

Solution 2

Using WITH for CTEs requires the previous statement to be terminated with ;. Using it at the start like this guarantees correct syntax

So does MERGE in SQL Server 2008

See this SO question: Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon

Solution 3

It is best practise to terminate every SQL statement with a semicolon. The SQL Server docs (for example here) suggest doing so will be mandated in a future version to there's really no excuse for not getting into the habit now.

To answer the question: you see ;WITH... on Stackoverflow because EITHER the person answering is a sloppy coder OR the person answering assumes the person asking the question is a sloppy coder (and they'll claim it is the latter when it is the former :) The definition of "sloppy coder" here is someone who only uses a semicolon when they are forced to do so.

Solution 4

The use of WITH is for common table expressions (CTEs). They were trying to force the CTE to be defined as the first statement (i.e. cannot be linked with other parts of the query hence the ;)

View more solutions

Comments

[JV.]

Transact-SQL Syntax Conventions (msdn.microsoft.com/en-us/library/ms177563.aspx) "Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version."