“赶上” SQL Server 2005中用户定义函数中的错误 [英] "Catching" Errors from within a user defined function in SQL Server 2005

问题描述

我有一个函数，将数字作为输入并将其转换为日期。此数字不是日期数字的任何标准形式，因此我必须手动将数字部分细分为各种日期部分，将日期部分转换为varchar字符串，然后将字符串连接并转换为新的datetime对象。 p>

我的问题是如何捕捉转换失败并从函数中返回空值或低范围值？我希望函数被动失败，返回默认值，而不是将失败代码返回到存储过程。 TRY / CATCH语句显然不能在函数内使用（除非我不知道某种类型的定义标志），并且尝试使用标准的'@@ Error<> 0'方法也不起作用。

解决方案

顺便说一句，这听起来像是标量UDF。正如Alex的博客所指出的，这是一场性能灾难。 http：// sqlblog .com / blogs / alexander_kuznetsov / archive / 2008/05/23 / reuse-your-code-with-cross-apply.aspx

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.

解决方案

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

这篇关于“赶上” SQL Server 2005中用户定义函数中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！