With in sql query

Question

hi all

  what is the use of WITH keyword in SQL.

 

;WITH Stored_Procedure_List AS (
SELECT 

what is use of semi colon(;) in the query.

Thanks in advance.

Answer 1

WITH denotes the start of a Common Table Expression (cte).

The statement before CTE declaraton should be closed/ended properly. As, ending statement with ';' is optional , many developers may not use it.

So, ';' is prefixed before CTE expression , to avoid errors.

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

This defines a SQL CTE query

You can think of WITH just like USING in C# or VB.NET

You can use the label after with as a table name in the script more than once

The select query in the CTE can also be used like a subquery too

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 3

CTE definition starts with "WITH":

http://www.sqlusa.com/bestpractices2005/cte/

You put the outer (main) query following the CTE definition.

CTE also requires a ";" on the previous statement.

To play it safe, CTE definition can start with ";WITH ...".

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

Sorry I just now catch the point in your question.

Since WITH is used for defining hints in SQL queries, it may be required to seperate the CTE expression from previous SQL commands

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 5

With can be either used in CTE or to define a Result set (in SQL 2012)

The syntax that you wrote is for CTE and in fact it doesn't start  with a semicolon. The rule for CTE is precedent line should be semicolon terminated (if it is a select, update or delete query)

---

Satheesh

Answer 6

WITH can either denote the start of a common table expression (CTE) or a locking hint so in the case of a CTE the semi colon is mandatory to end the statement preceding its declaration so the SQL query parser can determine its purpose.

I will add to the conversation that not ending statements with a semicolon has been deprecated as of SQL 2012. According to Microsoft's release schedule, features may be removed any time after two versions from the time of deprecation.  This means that, while not ending statements with a semicolon will still work for SQL 2012, developers should really get in the habit of using semicolons since code may cease to function without them at some point in the future.

For reference: features deprecated with SQL 2012 - http://technet.microsoft.com/en-us/library/ms143729.aspx

Cheers,

Andre Ranieri