Handle error record from User Defined Function

Question

Handle error record from User Defined Function

ssskumar4u

SSChasing Mays

Points: 607
More actions
July 29, 2013 at 5:44 am

#275735

Hi,
Please let me know if we can handle error record from User defined function.
SQL script enclosed with this mail.
regards,
Kumar.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

No you can't, but you can fix the function so that it doesn't pass invalid values to substring or whatever.

Can you confirm what the function is supposed to do? With the sample data provided - thanks for a great post (perfection would be embedding your script rather than attaching) - it looks as though you're grabbing the rightmost number from the pattern string where the pattern string matches all or part of SourceIndex, reading both strings from the left. Is this the case or is it coincidence?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ssskumar4u SSChasing Mays Points: 607 More actions · Answer 2

Hi Chris,

Thanks for your response.

It is the case that based on pattern matching, we have to retrieve the index number.

If pattern matching fails,then identify the error record for applying corrective action.

I tried with TRY and CATCH block error handling,but not successful.

Please let me know if you have any inputs for achieving the desired result.

Regards,

Kumar.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ssskumar4u (7/29/2013)
Hi Chris,
Thanks for your response.
It is the case that based on pattern matching, we have to retrieve the index number.
If pattern matching fails,then identify the error record for applying corrective action.
I tried with TRY and CATCH block error handling,but not successful.
Please let me know if you have any inputs for achieving the desired result.
Regards,
Kumar.

Of course 😀

Try this:

SELECT g.SourceIndex, g.IndexIdentifier, x.IndexNum

FROM tbl_GetIndex g

CROSS APPLY (SELECT IndexNum = CASE

WHEN CHARINDEX(IndexIdentifier,SourceIndex,0) = 1

THEN REVERSE(SUBSTRING(REVERSE(IndexIdentifier),2,CHARINDEX('(',REVERSE(IndexIdentifier),0)-2))

ELSE NULL END) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ssskumar4u SSChasing Mays Points: 607 More actions · Answer 4

ssskumar4u

SSChasing Mays

Points: 607

July 29, 2013 at 10:49 am

#1636711

Thanks a lot Chris !!!

Regards,

Kumar