CTE Syntax using WITH

What is with, WITH? If you don’t know watch Steve’s quick 2 minute video explanation.

Transcription of video:

Okay, so the syntax of using common table expressions is to use the WITH keyword. all CTE start with the word WITH rather than select like you would normally have in your queries. And it can be confusing if you’re assuming that the query to select data would normally start with a select. And the scope of a CTE is confined to a single query. Now, keeping that in mind, if you create a temp table that can be used throughout your session, if you create like a global temp table, that’s something that you can use from anywhere on the SQL Server. Views can be used by many people on different sessions, but a CTE is really a single SQL statement. Once that statement is done running, everything associated with the CTE goes away. Now, in the beginning, CTE seemed just a little bit weird until you master the syntax. I remember the very first time I looked at common table expressions, I just thought, “What the heck is this query doing?”

Let’s take a look at the basic syntax of CTE here. You start out using the keyword of WITH, with expression name, and then expression name is the name of the CTE. And then you have the option of naming the columns that the CTE is going to return as, and then inside of that as in the open parentheses, close parentheses, that’s where you put a query, similar to what you would put in a sub query, that’s the actual CTE query.

And then you select columns from that same expression name that was in the with statement up top. Now, one of the things you’ll notice that’s different here is that I put a semicolon at the very beginning of this. And one of the things with CTE is, is that if you’re running them in a batch with other statements, the previous statement has to be terminated with a semicolon. Now, normally, you don’t have to terminate all your SQL statements with a semicolon. But in order for CTEs to work, the statement before has to be terminated with a semicolon. So just to be safe. I always got in the habit of putting a semicolon at the beginning. And that way, if somebody forgets to terminate the statement before it doesn’t break things

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!