January 16, 2013 at 6:12 am
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.
January 16, 2013 at 6:18 am
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
January 16, 2013 at 9:06 am
Interesting.
I can't believe I've never tried to put a TRY/CATCH in a function before.
January 16, 2013 at 9:22 am
January 16, 2013 at 9:57 am
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
January 16, 2013 at 9:58 am
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!
January 16, 2013 at 10:49 am
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
January 16, 2013 at 11:29 am
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.
January 16, 2013 at 1:18 pm
An easy and straightforward one - thanks!
January 16, 2013 at 3:37 pm
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
January 17, 2013 at 12:47 am
I never tried using TRY ..CATCH in a function. But today i learnt we can't use it.
--
Dineshbabu
Desire to learn new things..
January 18, 2013 at 10:34 am
Easy One
January 18, 2013 at 11:06 am
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 13 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy