TRY...CATCH

Question

TRY...CATCH

January 16, 2013 at 8:38 am

This was removed by the editor as SPAM

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

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

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/

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 2

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

sestell1 SSChampion Points: 10230 More actions · Answer 3

Interesting.

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

EL Jerry SSCertifiable Points: 7061 More actions · Answer 4

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

Miles Neale SSChampion Points: 13147 More actions · Answer 6

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!

mtassin SSC-Insane Points: 23099 More actions · Answer 7

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]

Narud SSCrazy Points: 2837 More actions · Answer 8

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.

Revenant SSC-Forever Points: 42467 More actions · Answer 9

Revenant

SSC-Forever

Points: 42467

January 16, 2013 at 1:18 pm

#1578017

An easy and straightforward one - thanks!

webrunner SSC-Dedicated Points: 31718 More actions · Answer 10

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 11

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

--
Dineshbabu
Desire to learn new things..

asifkareem Ten Centuries Points: 1089 More actions · Answer 12

asifkareem

Ten Centuries

Points: 1089

January 18, 2013 at 10:34 am

#1578804

Easy One

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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