用户定义函数中的错误处理 [英] Error Handling in User Defined Functions

问题描述

我想在SQL Server 2005中编写一个非CLR用户定义的函数。该函数使用一个输入字符串并返回一个输出字符串。如果输入的字符串无效，那么我想向调用者指出一个错误。

我的第一个想法是使用RAISERROR引发异常。但是，SQL Server不允许在UDF内部（尽管可以在基于CLR的UDF中引发异常，请参见图）。

如果输入值有错误，我最后的办法是从函数返回NULL（或其他一些错误指示符值）。但是，我不喜欢这个选项：

1. 不向调用者提供任何有用的信息


2. 不允许我返回NULL以响应有效的输入（因为它被用作错误代码）。

解决方案

似乎SQL服务器UDF在这个（和许多其他）方式有一点限制。

你真的不能做很多事情 - 这是（现在）只是方式它是。您可以定义UDF，以便您可以通过返回值来返回错误条件（例如，在出现错误的情况下返回NULL），否则几乎不得不诉诸于编写存储过程，这可能会更多的错误处理和允许RAISERROR等等。

所以要么设计你的UDF不需要特定的错误条件的信号，那么你必须重新构建你的使用存储过程的方法（可以有多个OUTPUT参数，因此还可以返回错误代码以及数据有效负载，如果需要），或者您的UDF的管理CLR代码。

$ b $对不起，我没有一个更好的主意 - 现在，恐怕这些是你的选择 - 拿你的选择。

Marc

I want to write a non-CLR user-defined function in SQL Server 2005. This function takes an input string and returns an output string. If the input string is invalid, then I want to indicate an error to the caller.

My first thought was to use RAISERROR to raise an exception. However, SQL Server does not allow this inside a UDF (though you can raise exceptions in CLR-based UDFs, go figure).

My last resort would be to return a NULL (or some other error-indicator value) from the function if the input value is in error. However, I don't like this option, as it:

1. Doesn't provide any useful information to the caller
2. Doesn't allow me to return a NULL in response to valid input (since it's used as an error code).

Is there any caller-friendly way to halt a function on an error in SQL Server?

解决方案

It seems that SQL Server UDF's are a bit limited in this (and many other) way.

You really can't do a whole lot about it - that's (for now) just the way it is. Either you can define your UDF so that you can signal back an error condition by means of its return value (e.g. returning NULL in case of an error), or then you would almost have to resort to writing a stored procedure instead, which can have a lot more error handling and allows RAISERROR and so forth.

So either design your UDF to not require specific signaling of error conditions, or then you have to re-architect your approach to use stored procedures (which can have multiple OUTPUT parameters and thus can also return error code along with your data payload, if you need that), or managed CLR code for your UDF's.

Sorry I don't have a better idea - for now, I'm afraid, those are your options - take your pick.

Marc

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