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 8:38 am
This was removed by the editor as SPAM
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 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply