TRY...CATCH

  • ronmoses (1/16/2013)


    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

    Trust me, Ron - if it would have worked, you would have been appalled at the (lack of) performance.


    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/

  • Hugo Kornelis (1/16/2013)


    Trust me, Ron - if it would have worked, you would have been appalled at the (lack of) performance.

    Oh, for sure. I would never have used it on a large number of records, or implemented it in production. This was a special case involving about 500 records, one of which was throwing a conversion error. Seemed like a better option than combing through them.

    Yes, it could have been scripted SP-style, but these were records being returned by a rather complex query someone else wrote; I was merely trying to help with the error. So I didn't have ready access to the underlying records, or else I would have gone that way.

    ron

    -----
    a haiku...

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

  • This was removed by the editor as SPAM

  • Interesting.

    I can't believe I've never tried to put a TRY/CATCH in a function before.

  • Nice question, did a little research on this to get it right, though I did do a double-read-and-analyze.

    Saludos,

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Thanks for the question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good question. Had to read it twice to understand, so again it was knowing something and being careful to read it closely.

    +1

    Thanks

    M.

    Not all gray hairs are Dinosaurs!

  • Blah... I hate to get it wrong because I absentmindedly clicked on False and didn't pay attention and clicked submit when I was trying to select True.

    Lol... -1 point for me 🙂



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

  • Thanks for this good question. Recently I realized this when i try to use TRY CATCH to validate an insert operation inside a table function.

  • An easy and straightforward one - thanks!

  • I admit I didn't know this. Thanks for the knowledge.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I never tried using TRY ..CATCH in a function. But today i learnt we can't use it.

    --
    Dineshbabu
    Desire to learn new things..

  • Easy One

  • Nice straightforward question.

    I find it a little irritating that try...catch is banned in multi-statement functions; I like to have clean eror management as much as possible, and cases where it isn't possible tend to irritate me. I guess it's a performance trade-off, though.

    Tom

Viewing 14 posts - 16 through 29 (of 29 total)

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