December 28, 2008 at 3:44 pm
Jeffrey Williams (12/28/2008)
Christian - the semi-colon is required before a CTE WITH statement. I personally prefer prefixing the WITH instead of relying upon the previous statement ending with a semi-colon. I have found that others will not include the ending semi-colon, no matter how much I tell them it is going to cause them problems.I have had several individuals already ask with the CTE is failing after they have made a minor change. In every case, it came down to that developer inserting code before the CTE and not ending it with a semi-colon.
Hi Jeffrey,
I might be nitpicking here again, but the semicolon is required after the preceding statement, not before the WITH statement.
Of course the end result is a semicolon between the two statements, but I want to emphasize again that the semicolon ENDs a statement and does not START a statement. Putting the semicolon in front of the WITH just adds additional confusion.
If they do not include the semicolon, they will realize immediately during unit testing, and they will also get a detailed explanation of the error, so I kind of cannot not accept this excuse. And to be honest, adding a semicolon after each statement should not be that big of a deal.
Just as a simple comparison, you also would not precede the creation of a view with a GO, just because CREATE VIEW must be the first statement within a batch... (Of course you would do it if you had multiple batches, but not if you only create the single view)
Best Regards,
Chris Büttner
December 28, 2008 at 5:13 pm
Christian Buettner (12/28/2008)
Jeffrey Williams (12/28/2008)I might be nitpicking here again, but the semicolon is required after the preceding statement, not before the WITH statement.
[Of course the end result is a semicolon between the two statements, but I want to emphasize again that the semicolon ENDs a statement and does not START a statement.
Ah, and can you describe what the difference is between these two things?
In actual fact, despite how the programmers of C-derived languages (C, C++, Java, C#, etc.) habitually describe it, the semi-colon is neither a statement terminator, nor a statement initiator in SQL (nor even in most of those languages). Rather the ";" is a statement seperator and as such, it is perfectly valid to put it either at the beginning or at the end of a statement.
And given how SQL treats them (i.e., it only throws an error when a semicolon fails to precede certain statements), it make perfect sense to put the at the beginning of your SQL statements, if that is the only place that you use them.
I, like Jeff and others, do not use them normally, except where required: preceding WITH and SEND. And like them I put them at the beginning of the WITH/SEND, because they are the statements that require them and I do not want them to get lost if I, or someone else using my code, wants to cut and paste stuff.
I also put them there because it looks odd and I want to call attention to it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 28, 2008 at 5:22 pm
Christian Buettner (12/28/2008)
Putting the semicolon in front of the WITH just adds additional confusion.
This is only true if all of the other SQL statements in the same procedure or script end with ";". Then it should go at the end, as convention dictates.
However, if the only place that semicolons appear in a script is a the end of a statement before a WITH or SEND statement, then that is truly confusing and obscure. By putting in front of the WITH/SEND, it makes it clear that the semicolon is there solely because those statements require it to be there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 28, 2008 at 6:11 pm
Christian Buettner (12/28/2008)
Conventions aid us in that they remove decision processes from us. Instead of having do decide for each statement whether a semicolon is necessary, you just use it all the time and don't have to waste your brainpower. The unfortunate thing is only that we have learned not to use these semicolons. So it requires us "oldies" reprogramming our brains which is always tedious.
Have you ever written lengthy code in Oracle? Sometimes the same thing requires a semi-colon and sometimes it doesn't depending on where it's used and how it's used. And, sometimes you need a BEGIN/END and sometimes you don't. And some things work outside the BEGIN/END and not inside because it's not PL/SQL... all in the same proc or package or whatever.
So far as the WITH for CTE's needing the statement before it to have a semi-colon... that's just crud on the part of ANSI standards. They could have called it something else, but, no.... they had to call it WITH. :sick:
So far as I'm concerned, good languages shouldn't need us to identify where the end or start of somthing is. They sure as hell shouldn't need a semi-colon at the end of each line... but, I'm not a GUI programmer. I just wish they'd stop trying to turn SQL into a GUI language. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2008 at 7:25 pm
Christian Buettner (12/28/2008)
The reason they implemented this now is that the WITH has more than one meaning.
Actually, I doubt that that is the reason, otherwise why would SEND & RECEIVE which both have only one meaning also require it?
Contrawise, WAITFOR has a new/additional meaning (preceding RECEIVE) but does not require it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2008 at 2:43 am
Hi RBarryYoung,
Why do you think it is not a statement terminator?
;
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.
http://msdn.microsoft.com/en-us/library/ms177563.aspx
SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar
http://en.wikipedia.org/wiki/SQL
Best Regards,
Chris Büttner
December 29, 2008 at 2:52 am
RBarryYoung (12/28/2008)
Christian Buettner (12/28/2008)
Putting the semicolon in front of the WITH just adds additional confusion.This is only true if all of the other SQL statements in the same procedure or script end with ";". Then it should go at the end, as convention dictates.
However, if the only place that semicolons appear in a script is a the end of a statement before a WITH or SEND statement, then that is truly confusing and obscure. By putting in front of the WITH/SEND, it makes it clear that the semicolon is there solely because those statements require it to be there.
That is perfectly true and exactly what I am trying to do most of the time. Not that it works all the time (i mentioned before that reprogramming the brain is tedious), but that's how it should be.
Starting a SQL Statement with a semicolon is nothing more than a "dirty" workaround for not ending the previous statement correctly.
And to be honest, I did use it the same way, until I started using semicolons at the end of statements "almost" regularly.
Best Regards,
Chris Büttner
December 29, 2008 at 3:21 am
Jeff Moden (12/28/2008)
Christian Buettner (12/28/2008)
Conventions aid us in that they remove decision processes from us. Instead of having do decide for each statement whether a semicolon is necessary, you just use it all the time and don't have to waste your brainpower. The unfortunate thing is only that we have learned not to use these semicolons. So it requires us "oldies" reprogramming our brains which is always tedious.Have you ever written lengthy code in Oracle? Sometimes the same thing requires a semi-colon and sometimes it doesn't depending on where it's used and how it's used. And, sometimes you need a BEGIN/END and sometimes you don't. And some things work outside the BEGIN/END and not inside because it's not PL/SQL... all in the same proc or package or whatever.
So far as the WITH for CTE's needing the statement before it to have a semi-colon... that's just crud on the part of ANSI standards. They could have called it something else, but, no.... they had to call it WITH. :sick:
So far as I'm concerned, good languages shouldn't need us to identify where the end or start of somthing is. They sure as hell shouldn't need a semi-colon at the end of each line... but, I'm not a GUI programmer. I just wish they'd stop trying to turn SQL into a GUI language. 😀
Unfortunately I had not much exposure to PL/SQL yet. But I am currently in the process of changing that. I'll have to watch out for these issues then.
Regarding the requirement of statement terminators - Maybe, in a perfect language, the terminator would not be required. But reality tells us that we are using a language that is not "fix". It has grown and still is growing. If we would rewrite the whole language and were not dependent on following a standard as good as possible, then we might be able to get rid of these terminators again.
But are they really that problematic? I don't think so. You don't even have to think once you have adopted the semicolon. It just goes there "naturally" after a few weeks of training. But you need to do the training consistently, only then will you adopt it.
Best Regards,
Chris Büttner
December 29, 2008 at 3:50 am
RBarryYoung (12/28/2008)
Christian Buettner (12/28/2008)
The reason they implemented this now is that the WITH has more than one meaning.Actually, I doubt that that is the reason, otherwise why would SEND & RECEIVE which both have only one meaning also require it?
Contrawise, WAITFOR has a new/additional meaning (preceding RECEIVE) but does not require it.
OK, I must recall my statement here.
Since I do not know the reason, I can only say that I "think" that because WITH can also be used in a SELECT statement, the parser needs to know when the previous statement ends. But that is just guessing and therefore I recall my statement.
Best Regards,
Chris Büttner
December 29, 2008 at 8:23 am
Christian Buettner (12/29/2008)
Hi RBarryYoung,Why do you think it is not a statement terminator?
Still waiting for your response to my question:
RBarryYoung (12/28/2008)
Christian Buettner (12/28/2008)
I might be nitpicking here again, but the semicolon is required after the preceding statement, not before the WITH statement.
Of course the end result is a semicolon between the two statements, but I want to emphasize again that the semicolon ENDs a statement and does not START a statement.
Ah, and can you describe what the difference is between these two things?
🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2008 at 8:34 am
Christian Buettner (12/28/2008)
Jeff Moden (12/28/2008)
...Lordy, I hope they never make SQL Server require semi-colons for everything like {gasp} Oracle...Hm I kind of like that approach with semicolon after each statement, but that's probably personal taste... Instead of having do decide for each statement whether a semicolon is necessary, you just use it all the time and don't have to waste your brainpower. The unfortunate thing is only that we have learned not to use these semicolons. So it requires us "oldies" reprogramming our brains which is always tedious...
Having been an Oracle DBA/developer (and other databases) longer than I've been a SQL Server DBA/developer, I think it's probably good practice to follow trends that are closer to the SQL standard than the local flavors that each vendor supports. I still put the INTO in the INSERT statement, the FROM in the DELETE statement, etc, and I always have a BEGIN...END around my stored procedure code even though SQL Server doesn't require those things. Somehow my code just seems naked without them. :blink:
December 29, 2008 at 12:18 pm
Jeff Moden (12/28/2008)
Thanks, Chris. Yeah, I know it looks unusual... WITH is the only place where a semi-colon is actually required before something and since I don't use semi-colons anywhere else in my code, I just include it as part of the WITH even if it's a standalone.Lordy, I hope they never make SQL Server require semi-colons for everything like {gasp} Oracle and some of the programming languages do.
Sorry to say - it's coming.... ^%%$#@$^%&* semicolons will eventually be required on all SQL statements. You can thank the ANSI board for that one.
As far as I can recall - it's on the plate for 2011....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 29, 2008 at 12:49 pm
Matt Miller (12/29/2008)
Sorry to say - it's coming.... ^%%$#@$^%&* semicolons will eventually be required on all SQL statements. You can thank the ANSI board for that one.
Yep, step number 251 in the "Plan to Make All Languages Look Like 'C'." This is a natural consequence of having only compiler writers and academics on the committees.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2008 at 4:48 pm
RBarryYoung (12/29/2008)
Christian Buettner (12/29/2008)
Hi RBarryYoung,Why do you think it is not a statement terminator?
Still waiting for your response to my question:
RBarryYoung (12/28/2008)
Christian Buettner (12/28/2008)
I might be nitpicking here again, but the semicolon is required after the preceding statement, not before the WITH statement.
Of course the end result is a semicolon between the two statements, but I want to emphasize again that the semicolon ENDs a statement and does not START a statement.
Ah, and can you describe what the difference is between these two things?
🙂
I am not sure what your intention is here, but the difference is where the terminator is located.
Just like you end questions with a questionmark. Or would you start sentences after a question with a question mark like this
?Looks odd, doesn't it ?
Best Regards,
Chris Büttner
December 29, 2008 at 5:56 pm
RBarryYoung (12/29/2008)
Matt Miller (12/29/2008)
Sorry to say - it's coming.... ^%%$#@$^%&* semicolons will eventually be required on all SQL statements. You can thank the ANSI board for that one.Yep, step number 251 in the "Plan to Make All Languages Look Like 'C'." This is a natural consequence of having only compiler writers and academics on the committees.
Heh... more like purple squirrels and gerble's ... they forget that if you make something totally idiot proof, only idiots will use it. 😛 Of course, look who're on that board... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply