TRY...CATCH

  • rhythm.varshney

    Ten Centuries

    Points: 1138

    Comments posted to this topic are about the item TRY...CATCH

  • Lokesh Vij

    SSChampion

    Points: 10836

    Easy one for the day. Thanks 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • demonfox

    SSCertifiable

    Points: 6289

    for missing explanation ; I think Funtion is designed to return a value . When error handling is allowed , suppose an exception is raised , the return value is bound to restrictions . That may have been the reason to not allow the try catch handling in sql server.

    please correct me if I am wrong.

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • sqlnaive

    SSCoach

    Points: 17435

    Good question brushin up your skills. 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice question, thanks! Explanation is short, but correct 😀

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

  • Stuart Davies

    SSCoach

    Points: 18874

    Nice and straightforwards - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Good question. I had to do a bit of research - I knew it wasn't allowewd in inline functions, but was unsure about multi-statement and scalar functions.

    The link provided in the explanation is for SQL Server 2005; here are two links for the current version:

    http://msdn.microsoft.com/en-us/library/ms175976.aspx

    http://msdn.microsoft.com/en-us/library/ms186755.aspx


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • paul s-306273

    SSChampion

    Points: 10615

    Ouch - read this too quickly and 'saw' cannot as can...

  • Gazareth

    One Orange Chip

    Points: 27737

    Nice question, good to remember.

    I'm struggling to think what you'd be doing in a function that would require a TRY... CATCH 🙂

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Gazareth (1/16/2013)


    I'm struggling to think what you'd be doing in a function that would require a TRY... CATCH 🙂

    Data conversions with possibly unclean data; computations that could generate an overflow, underflow, or divide by zero condition, etc.

    Or maybe even lookups in a table that might not exist (I shudder at the thought of having to maintain a database where that is a serious possibility - but I've seen worse)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Gazareth

    One Orange Chip

    Points: 27737

    Hugo Kornelis (1/16/2013)


    Gazareth (1/16/2013)


    I'm struggling to think what you'd be doing in a function that would require a TRY... CATCH 🙂

    Data conversions with possibly unclean data; computations that could generate an overflow, underflow, or divide by zero condition, etc.

    Or maybe even lookups in a table that might not exist (I shudder at the thought of having to maintain a database where that is a serious possibility - but I've seen worse)

    Fair enough, although I'm not sure I personally would be using functions for most of those; and if I did I would probably be using other methods than TRY... CATCH to deal with them.

    But that's just me. 🙂

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Thanks for the great question. Had to research it to get it right.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    negative questions make true to be false and vice versa... should've double read an easy question :Whistling:

  • Ajay Reddy

    Right there with Babe

    Points: 750

    nice question 🙂

    Thanks&Regards
    AJAY REDDY.L

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    I just encountered this scenario this past week. The ISDATE() and ISNUMERIC() functions are practically useless when determining whether a string will safely convert to a datetime or numeric value, respectively. (For example, '$' passes ISNUMERIC().) So I figured why not put a TRY...CATCH in a function, try to convert the value, return 0 if it fails and 1 if it doesn't. Bingo, working ISDATE(). But no... denied. Oh well.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

Viewing 15 posts - 1 through 15 (of 29 total)

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