Terminating with THROW

Question

Post reply

Terminating with THROW

Sean Pearce

SSCoach

Points: 15999
More actions
October 20, 2014 at 10:50 pm

#397537

Comments posted to this topic are about the item Terminating with THROW
The SQL Guy @ blogspot[/url]
@SeanPearceSQL
About Me[/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Very excellent question.

Thanks Sean.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Nice question.

I started reading and thought how odd it was to see tsql without semi colons these days....I soon realised why 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 3

Koen Verbeeck (10/21/2014)
Very excellent question.
Thanks Sean.

+1

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Umer Akram

SSC-Insane

Points: 22209

October 21, 2014 at 1:34 am

#1753676

great question Sean. Thanks for sharing

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

Thank you :Wow:

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

JaimePC SSC Eights! Points: 864 More actions · Answer 6

Very nice QOTD

Was good the explanation about semicolons 😀

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Excellent question. Caught me off guard but once I saw I was wrong I immediately noticed the aliased query. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 8

Excellent question, Sean, and fabulous explanation. It caught me off guard and made me go "WOW" when I read the explanation, because I usually don't like using semi-colons in my T-SQL code (except before a CTE). I had to go look at some recent code I had written using THROW to make sure I had used preceding semi-colons (I had). I will have to add THROW to my mental list of keywords that require a preceding semi-colon.

I'm surprised that the T-SQL compiler can't detect THROW as the start of a new statement. I can understand the need for the leading semi-colon for WITH, to differentiate from its usage introducing a table hint (e.g. "SELECT a, b, c FROM TableX WITH (NOLOCK)"), but I don't remember THROW being used anyplace else in T-SQL. Can someone explain why THROW can't just be a reserved word to avoid the need for a semi-colon?

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

stephen.long.1 (10/21/2014)
Excellent question, Sean, and fabulous explanation. It caught me off guard and made me go "WOW" when I read the explanation, because I usually don't like using semi-colons in my T-SQL code (except before a CTE). I had to go look at some recent code I had written using THROW to make sure I had used preceding semi-colons (I had). I will have to add THROW to my mental list of keywords that require a preceding semi-colon.
I'm surprised that the T-SQL compiler can't detect THROW as the start of a new statement. I can understand the need for the leading semi-colon for WITH, to differentiate from its usage introducing a table hint (e.g. "SELECT a, b, c FROM TableX WITH (NOLOCK)"), but I don't remember THROW being used anyplace else in T-SQL. Can someone explain why THROW can't just be a reserved word to avoid the need for a semi-colon?

A cte does NOT require it to start with a semicolon. The semicolon is statement terminator not a statement beginninator (nod to Lynn Pettis for that phrase). A cte, just like THROW, requires that the previous statement be terminated with a semicolon.

I usually don't like using semi-colons in my T-SQL code

You should start. It is the ANSI standard and at some point sql server will require it. Notice that all the new features already require it usage.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Emil B SSCertifiable Points: 5659 More actions · Answer 10

This example has been tested on SQL Server 2012 and will not work on 2008 R2 or earlier

Actually this script WILL work on sql server 2008 because THROW is not a statement here

Miles Neale SSChampion Points: 13147 More actions · Answer 11

Excellent question, and you got me. Learned from it - Thanks....

Not all gray hairs are Dinosaurs!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

Thank you, Sean, for the awesome post, very interesting one.

(out of three choices, I was knowing 2, and for the returned resultset is null or empty there I was having doubts, so I have to execute the query to clarify my doubt. But overall I got the other 2 choices for wrong reason which came to know after reading the explanation, well in the end learnt the new stuff. 🙂 )

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Toreador SSChampion Points: 11368 More actions · Answer 13

Sean Lange (10/21/2014)
A cte does NOT require it to start with a semicolon. The semicolon is statement terminator not a statement beginninator (nod to Lynn Pettis for that phrase). A cte, just like THROW, requires that the previous statement be terminated with a semicolon.
...
You should start. It is the ANSI standard and at some point sql server will require it. Notice that all the new features already require it usage.

+1 to all this!