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.
我想在SQL Server 2005中编写一个非CLR用户定义函数。该函数接受一个输入字符串并返回一个输出字符串。如果输入字符串无效，那么我想向调用者指出错误。
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).
我的第一个想法是使用RAISERROR来引发异常。但是，SQL Server不允许在UDF中使用它（尽管您可以在基于CLR的UDF中引发异常，如图所示）。
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:
我的最后一种方法是在输入值出错时从函数返回NULL（或其他一些错误指示符值）。但是，我不喜欢这个选项，因为它：
- Doesn't provide any useful information to the caller
- 不向呼叫者提供任何有用的信息
- Doesn't allow me to return a NULL in response to valid input (since it's used as an error code).
- 不允许我在响应有效输入时返回NULL（因为它被用作错误代码）。
Is there any caller-friendly way to halt a function on an error in SQL Server?
是否有任何调用方友好的方法来暂停SQL Server中的错误函数？
2 个解决方案
#1
7
It seems that SQL Server UDF's are a bit limited in this (and many other) way.
似乎SQL Server UDF在这个（以及许多其他方式）方面有点受限。
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.
你真的不能做很多事情 - 那就是（现在）它的方式。要么你可以定义你的UDF，以便你可以通过它的返回值发出错误信号（例如，在出现错误时返回NULL），或者你几乎不得不求助于编写存储过程，这可能有更多错误处理并允许RAISERROR等等。
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.
因此要么设计你的UDF不需要特定的错误条件信号，要么你必须重新设计你的方法来使用存储过程（它可以有多个OUTPUT参数，因此也可以返回错误代码和数据有效负载，如果你需要），或管理您的UDF的CLR代码。
Sorry I don't have a better idea - for now, I'm afraid, those are your options - take your pick.
对不起，我没有更好的主意 - 现在，我担心，这些是你的选择 - 接受你的选择。
Marc
渣子
#2
1
There's a possible solution given in an answer to a duplicate question here, based on this idea:
基于这个想法，在这里回答一个重复的问题时给出了一个可能的解决方案：
return cast('Error message here.' as int);
Which throws something like this:
抛出这样的东西：
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error message here.' to data type int.
It works OK for scalar-valued UDFs, but not for table-valued ones.
它适用于标量值UDF，但不适用于表值UDF。
#1
7
It seems that SQL Server UDF's are a bit limited in this (and many other) way.
似乎SQL Server UDF在这个（以及许多其他方式）方面有点受限。
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.
你真的不能做很多事情 - 那就是（现在）它的方式。要么你可以定义你的UDF，以便你可以通过它的返回值发出错误信号（例如，在出现错误时返回NULL），或者你几乎不得不求助于编写存储过程，这可能有更多错误处理并允许RAISERROR等等。
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.
因此要么设计你的UDF不需要特定的错误条件信号，要么你必须重新设计你的方法来使用存储过程（它可以有多个OUTPUT参数，因此也可以返回错误代码和数据有效负载，如果你需要），或管理您的UDF的CLR代码。
Sorry I don't have a better idea - for now, I'm afraid, those are your options - take your pick.
对不起，我没有更好的主意 - 现在，我担心，这些是你的选择 - 接受你的选择。
Marc
渣子
#2
1
There's a possible solution given in an answer to a duplicate question here, based on this idea:
基于这个想法，在这里回答一个重复的问题时给出了一个可能的解决方案：
return cast('Error message here.' as int);
Which throws something like this:
抛出这样的东西：
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error message here.' to data type int.
It works OK for scalar-valued UDFs, but not for table-valued ones.
它适用于标量值UDF，但不适用于表值UDF。