September 20, 2011 at 6:20 am
Brandie Tarvin (9/20/2011)
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
I was going to reply but I encountered a stack overflow trying to unwind the negatives in that sentence. 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 6:23 am
SQL Kiwi (9/20/2011)
Brandie Tarvin (9/20/2011)
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.I was going to reply but I encountered a stack overflow trying to unwind the negatives in that sentence. 😛
So what's wrong with your internals? 😛
September 20, 2011 at 6:24 am
Ninja's_RGR'us (9/20/2011)
Obviously I wasn't clear in my writing (for a change :-D).
You were perfectly clear - I deliberately chose to misinterpret you. On the subject of MERGE:
WITH CHANGE_TRACKING_CONTEXT (0x)
MERGE master.dbo.spt_values AS A USING (VALUES(NULL)) AS V(v) ON NULL = NULL
WHEN MATCHED THEN DELETE
Msg 10713, Level 15, State 1, Line 3
A MERGE statement must be terminated by a semi-colon (;).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 6:26 am
Gianluca Sartori (9/20/2011)
That was nasty! I know very few people that can read that query and guess what it does.
There might be other ways to construct a "WITH ()" that doesn't use a SELECT, but I couldn't think of one!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 6:33 am
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
Self plug...
This is a unique event where you can mix business with pleasure. Think about it, one day you are sipping a Pina colada or a margarita on a white sandy beach and the next day you are learning SQL Server. 🙂
One day you are scuba diving with my CTO as your Dive master and the next day you are getti9ng tons of useful information about SQL Server. 😀
I am not going to mention about the carnival here at all. 😀 (It is the weekend before the SQL Saturday.)
-Roy
September 20, 2011 at 6:36 am
Ninja's_RGR'us (9/20/2011)
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
I'm registered!
Did you register already?
-Roy
September 20, 2011 at 6:38 am
Roy Ernest (9/20/2011)
Ninja's_RGR'us (9/20/2011)
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
I'm registered!
Did you register already?
YES!
I never said I was going, but I'm definitely registered (as not going). :smooooth:
September 20, 2011 at 6:45 am
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
The parser complains that the previous statement is not terminated with a ;, not that the with doesn't start with one.
Statements shouldn't start with statement terminators. Statements should end with statement terminators. Just like I don't start sentences with a ., I end them with one.
This is fine:
WITH SystemTables (object_id, index_id) AS (SELECT object_id, index_id FROM sys.indexes AS i WHERE type = 2
)
SELECT * FROM SystemTables
ORDER BY index_id;
This is also fine:
SELECT COUNT(*) FROM sys.indexes AS i WHERE type = 2;WITH SystemTables (object_id, index_id) AS (
SELECT object_id, index_id FROM sys.indexes AS i WHERE type = 2
)
SELECT * FROM SystemTables
ORDER BY index_id;
I tend to put a semicolon before WITH when I'm writing CTEs, even when I don't need one. Except where I can't, of course (definitions of Views, for example). It's not grammatically correct, but I find it a useful habit for ease-of-debugging.
Of course, I'm also one of the few people I know who tries to terminate every T-SQL statement with a semicolon. Not needed, but it keeps things tidy in my opinion, and MS does say it will be needed in a future version (http://msdn.microsoft.com/en-us/library/ms177563.aspx).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 20, 2011 at 7:21 am
Gianluca Sartori (9/20/2011)
SQL Kiwi (9/20/2011)
Ninja's_RGR'us (9/20/2011)
Try doing ;with () Without any select after the with and you'll see if the engine thinks the statement is complete ;-).Ok:
WITH CHANGE_TRACKING_CONTEXT (0x)
MERGE master.dbo.spt_values AS A USING (VALUES(NULL)) AS V(v) ON NULL = NULL
WHEN MATCHED THEN DELETE;
No SELECT there. I'm with Gail on the semicolon by the way.
That was nasty! I know very few people that can read that query and guess what it does.
On the semicolon, I'm with Gail too.
You mean there are people who think it does something?
Or does not changing any data, not returning any rows, and not influencing what future calls of changetable might return count as doing something? :hehe:
I too am with Gail on the semicolon, although I hate the thing and regard it as symptomatic of not looking after the language properly.
Also (this should be a separate post to help make up the 300, but I'm feeling lazy) I cry FOUL on Paul; the discussion was about CTEs, and WITH CHANGE_TRACKING_CONTEXT (0x) isn't a CTE at all. 😀
Tom
September 20, 2011 at 7:31 am
L' Eomot Inversé (9/20/2011)
Also (this should be a separate post to help make up the 300, but I'm feeling lazy) I cry FOUL on Paul; the discussion was about CTEs, and WITH CHANGE_TRACKING_CONTEXT (0x) isn't a CTE at all. 😀
Quite so, but I cheated. I do that sometimes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 7:31 am
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
Great Scott!
What are you using? It can't be SQL 2005, SQL 2008, or Denali. :w00t:
Tom
September 20, 2011 at 7:33 am
L' Eomot Inversé (9/20/2011)
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
Great Scott!
What are you using? It can't be SQL 2005, SQL 2008, or Denali. :w00t:
What's up with the new signature Tom? It sure looks like french but I'm pretty sure it's not!
September 20, 2011 at 7:33 am
Roy Ernest (9/20/2011)
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
Self plug...
This is a unique event where you can mix business with pleasure. Think about it, one day you are sipping a Pina colada or a margarita on a white sandy beach and the next day you are learning SQL Server. 🙂
One day you are scuba diving with my CTO as your Dive master and the next day you are getti9ng tons of useful information about SQL Server. 😀
I am not going to mention about the carnival here at all. 😀 (It is the weekend before the SQL Saturday.)
*sigh* Oh, if I could still pass a dive physical... so tempting...
-Ki
September 20, 2011 at 7:35 am
Kiara (9/20/2011)
Roy Ernest (9/20/2011)
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
Self plug...
This is a unique event where you can mix business with pleasure. Think about it, one day you are sipping a Pina colada or a margarita on a white sandy beach and the next day you are learning SQL Server. 🙂
One day you are scuba diving with my CTO as your Dive master and the next day you are getti9ng tons of useful information about SQL Server. 😀
I am not going to mention about the carnival here at all. 😀 (It is the weekend before the SQL Saturday.)
*sigh* Oh, if I could still pass a dive physical... so tempting...
Well now you can actually sell this as deep dive training! 😀
September 20, 2011 at 7:36 am
Ninja's_RGR'us (9/20/2011)
Well now you can actually sell this as deep dive training! 😀
*groan* Are we going to be drowning in puns now?
-Ki
Viewing 15 posts - 30,091 through 30,105 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply