TRY...CATCH

Question

TRY...CATCH

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

mtassin SSC-Insane Points: 23099 More actions · Answer 1

Hugo Kornelis (4/2/2013)
In short - just after I had learned myself to consistently semicolon-terminate every statement, I now have to leanr myself NOT to do that for at least one statement (END TRY).

+1 I am sometimes baffled by inconsistencies from Microsoft... this is just one of them. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 2

Hugo Kornelis (4/2/2013)
Good question, but the explanation falls a bit short. I was very disappointed when I first saw this behaviour. A semicolon is a statement terminator and should be used after every statement - but apparently, END TRY is not considered a statement by this logic. So the semicolon after END TRY is not interpreted as terminating the END TRY, but as terminating a dummy, zero-length statement. (They exist. Try typing a bunch of semicolons and hit execute - it will complete without errors).
In short - just after I had learned myself to consistently semicolon-terminate every statement, I now have to leanr myself NOT to do that for at least one statement (END TRY).

Thanks Hugo! I was scratching my head TRYing to figure this one out. Also another interesting semi-colon behavior, you must end the statement prior to the THROW with a semi-colon. I received an interesting error (on SQL 2012) when upping the severity and removing all semi-colons.

Msg 6401, Level 16, State 1, Line 13

Cannot roll back THROW. No transaction or savepoint of that name was found.

Revenant SSC-Forever Points: 42467 More actions · Answer 3

I like to terminate each statement with a semicolon and I made this mistake years ago: "Experience is an euphemism for our past mistakes."

Thanks, Venkat!

Venkat Jonnalagadda Mr or Mrs. 500 Points: 510 More actions · Answer 4

Revenant (4/2/2013)
I like to terminate each statement with a semicolon and I made this mistake years ago: "Experience is an euphemism for our past mistakes."

+1

I had the same tendency and made this mistake some time back. Had to do a lot of googling and reading to understand about the invisible statement between the End Try and Begin Catch other than the ; thats causing the problem..

___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

tom.kane Old Hand Points: 358 More actions · Answer 5

tom.kane

Old Hand

Points: 358

April 2, 2013 at 8:54 pm

#1603014

Good question. Thanks for posting!

Black_Knight SSCrazy Points: 2031 More actions · Answer 6

Good question - Had to think hard about that one...

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

Von Microsoft am 29.12.2009 um 12:11 bereitgestellt
I am resolving the corresonding SQL11 bug as "by design". Here is the explanation: The semicolon between END TRY and BEGIN CATCH should not be allowed, because they are actually not different statements, but parts of the same TRY-CATCH statement. We only allow semicolons when they separate two statements in a sequence. A word of explanation why then we allow semicolons after BEGIN TRY and BEGIN CATCH. These keywords serve as opening "parentheses" that start an embedded statement sequence. Semicolons after BEGIN TRY/BEGIN CATCH get parsed as part of that embedded sequence, with the first statement in the sequence being empty. While we allow this syntax, I would not recommend it as a good coding practice because it creates a wrong impression of BEGIN TRY/BEGIN CATCH being independent, standalone statements.

http://connect.microsoft.com/SQLServer/feedback/details/498085/msft-mso-end-try-throws-an-error-if-terminated-with-a-semicolon

Best Regards,

Chris Büttner

Stephen_W_Dodd SSCommitted Points: 1628 More actions · Answer 8

Chris,

Great reference, that clears up the whole conversation 😀

marlon.seton SSCrazy Points: 2623 More actions · Answer 9

Revenant (4/2/2013)
I like to terminate each statement with a semicolon and I made this mistake years ago: "Experience is an euphemism for our past mistakes."
Thanks, Venkat!

Surely our past mistakes are some of our experiences, so it's not a euphemism at all.

TomThomson SSC Guru Points: 104773 More actions · Answer 10

I had forgotten were not there that the ";" is not alowed in there.

And didn't even notice that THROW would cause an error on three of the four releases the question says it applies to.

Good question.

MS clealrly don't understand error management, if they did they would never have (a) introduced try...catch without including throw in the package or (b) excluded if...else statements and so on from the scope of try-catch. At least they got round to fixing (a) only seven years after they committed that error; but everyone still has to live with (b). Unless of course the statement in BoL is wrong!

Tom

Miles Neale SSChampion Points: 13147 More actions · Answer 11

Miles Neale

SSChampion

Points: 13147

April 5, 2013 at 11:56 am

#1604052

Nice!

Not all gray hairs are Dinosaurs!

Kevin Ray Say Hey Kid Points: 687 More actions · Answer 12

I do not know much about Try, Catch and Throw. But I got rid of all of the semicolons and the code ran without an error on MS-SMS 2008.

sqlnaive SSCoach Points: 17437 More actions · Answer 13

Bob Cullen-434885 (4/2/2013)
I selected the syntax error option but not for the reason stated. The question states SQL 2005 and higher, but THROW evidently makes its appearance with SQL 2012, so is invalid syntax is the right answer for 2005, 2008, 2008 R2, eh?
So, another case of "it depends".

+1.

I chose the correct answer for same reason. THROW is available only in SQL 2012. Apart from that better to know new things.

sqlnaive SSCoach Points: 17437 More actions · Answer 14

Christian Buettner-167247 (4/3/2013)
Von Microsoft am 29.12.2009 um 12:11 bereitgestellt
I am resolving the corresonding SQL11 bug as "by design". Here is the explanation: The semicolon between END TRY and BEGIN CATCH should not be allowed, because they are actually not different statements, but parts of the same TRY-CATCH statement. We only allow semicolons when they separate two statements in a sequence. A word of explanation why then we allow semicolons after BEGIN TRY and BEGIN CATCH. These keywords serve as opening "parentheses" that start an embedded statement sequence. Semicolons after BEGIN TRY/BEGIN CATCH get parsed as part of that embedded sequence, with the first statement in the sequence being empty. While we allow this syntax, I would not recommend it as a good coding practice because it creates a wrong impression of BEGIN TRY/BEGIN CATCH being independent, standalone statements.
http://connect.microsoft.com/SQLServer/feedback/details/498085/msft-mso-end-try-throws-an-error-if-terminated-with-a-semicolon

Thanks Chris for this reference. It cleared the confusion in my mind.

Arno Kwetters Hall of Fame Points: 3727 More actions · Answer 15

I don't get the explanation.

The ; after the END TRY produced the error.

There is no statement between END TRY and BEGIn CATCH !