"Catching" Errors from within a user defined function in SQL Server 2005

Question

I have a function that takes a number as an input and converts it to a date. This number isn't any standard form of date number, so I have to manually subdivide portions of the number to various date parts, cast the date parts to varchar strings and then, concatenate and cast the strings to a new datetime object.

My question is how can I catch a casting failure and return a null or low-range value from my function? I would prefer for my function to "passively" fail, returning a default value, instead of returning a fail code to my stored procedure. TRY/CATCH statements apparently don't work form within functions (unless there is some type of definition flag that I am unaware of) and trying the standard '@@Error <> 0' method doesn't work either.

Mark Sowul · Accepted Answer · 2011-03-18 15:25:25Z

2

Incidentally this sounds like it could be a scalar UDF. This is a performance disaster, as Alex's blog points out. http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

answered Mar 18, 2011 at 15:25

Mark Sowul

10.7k1 gold badge49 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wcm Over a year ago

Interesting link. I wasn't aware of the APPLY clause.

Ferruccio · Accepted Answer · 2011-03-18 15:33:27Z

2

SELECT CASE WHEN ISDATE(@yourParameter) = 1
   THEN CAST(@yourParameter AS DATETIME) 
   ELSE YourDefaultValue 
END

edited Mar 18, 2011 at 15:33

Ferruccio

101k38 gold badges232 silver badges305 bronze badges

answered Mar 18, 2011 at 15:20

A-K

17.1k8 gold badges58 silver badges77 bronze badges

1 Comment

wcm Over a year ago

Didn't RLH say that the number wasn't in any standard form of date? I don't think ISDATE will work in this case.

wcm · Accepted Answer · 2011-03-18 15:36:55Z

0

Since the format is nonstandard it sounds to me like you are stuck with doing all the validation yourself, prior to casting. Making sure that the individual pieces are numeric, checking that the month is between 1 and 12, making sure it's not Feb 30, etc. If anything fails you return nothing.

answered Mar 18, 2011 at 15:36

wcm

9,3417 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

"Catching" Errors from within a user defined function in SQL Server 2005

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related