Terminating with THROW

  • Sean Pearce

    SSCoach

    Points: 15750

    Comments posted to this topic are about the item Terminating with THROW

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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: 47068

    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);

  • 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]

  • Carlo Romagnano

    SSC-Insane

    Points: 21968

    Koen Verbeeck (10/21/2014)


    Very excellent question.

    Thanks Sean.

    +1

  • twin.devil

    SSC-Insane

    Points: 22208

    great question Sean. Thanks for sharing

  • Sean Pearce

    SSCoach

    Points: 15750

    Thank you :Wow:

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • JaimePC

    SSC Eights!

    Points: 864

    Very nice QOTD

    Was good the explanation about semicolons 😀

  • Sean Lange

    SSC Guru

    Points: 286536

    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: 2577

    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: 286536

    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/

  • This was removed by the editor as SPAM

  • Emil B

    SSCertifiable

    Points: 5568

    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

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

    Not all gray hairs are Dinosaurs!

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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: 11256

    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!

  • Viewing 15 posts - 1 through 15 (of 21 total)

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