如何从 SQL Server 用户定义函数报告错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1485034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to report an error from a SQL Server user-defined function
提问by EMP
I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns:
我正在 SQL Server 2008 中编写用户定义的函数。我知道函数不能以通常的方式引发错误 - 如果您尝试包含 RAISERROR 语句 SQL 返回：
Msg 443, Level 16, State 14, Procedure ..., Line ...
Invalid use of a side-effecting operator 'RAISERROR' within a function.
But the fact is, the function takes some input, which may be invalid and, if it is, there is no meaningful value the function can return. What do I do then?
但事实是，该函数需要一些输入，这些输入可能是无效的，如果是，则该函数无法返回任何有意义的值。那我该怎么办？
I could, of course, return NULL, but it would be difficult for any developer using the function to troubleshoot this. I could also cause a division by zero or something like that - this would generate an error message, but a misleading one. Is there any way I can have my own error message reported somehow?
当然，我可以返回 NULL，但是任何使用该函数的开发人员都很难解决这个问题。我也可能导致除以零或类似的东西 - 这会产生一条错误消息，但会产生误导。有什么办法可以让我以某种方式报告我自己的错误消息？
回答by Vladimir Korolev
You can use CAST to throw meaningful error:
您可以使用 CAST 抛出有意义的错误：
create function dbo.throwError()
returns nvarchar(max)
as
begin
return cast('Error happened here.' as int);
end
Then Sql Server will show some help information:
然后Sql Server会显示一些帮助信息：
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error happened here.' to data type int.
回答by Remus Rusanu
The usual trick is to force a divide by 0. This will raise an error and interrupt the current statement that is evaluating the function. If the developer or support person knows about this behavior, investigating and troubleshooting the problem is fairly easy as the division by 0 error is understood as a symptom of a different, unrelated problem.
通常的技巧是强制除以 0。这将引发错误并中断正在评估函数的当前语句。如果开发人员或支持人员知道这种行为，那么调查和排除问题就会很容易，因为除以 0 错误被理解为不同的、不相关的问题的症状。
As bad as this looks from any point of view, unfortunately the design of SQL functions at the moment allows no better choice. Using RAISERROR should absolutely be allowed in functions.
尽管从任何角度来看这看起来都很糟糕，但不幸的是，目前 SQL 函数的设计不允许有更好的选择。在函数中绝对应该允许使用 RAISERROR。
回答by satnhak
Following on from Vladimir Korolev's answer, the idiom to conditionally throw an error is
继弗拉基米尔·科罗廖夫 (Vladimir Korolev) 的回答之后，有条件地抛出错误的习语是
CREATE FUNCTION [dbo].[Throw]
(
@error NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
RETURN CAST(@error AS INT)
END
GO
DECLARE @error NVARCHAR(MAX)
DECLARE @bit BIT
IF `error condition` SET @error = 'My Error'
ELSE SET @error = '0'
SET @bit = [dbo].[Throw](@error)
回答by AndyM
I think the cleanest way is to just accept that the function can return NULL if invalid arguments are passed. As long is this is clearly documented then this should be okay?
我认为最干净的方法是接受函数可以在传递无效参数时返回 NULL。只要这是明确记录的，那么这应该没问题吗？
-- =============================================
-- Author: AM
-- Create date: 03/02/2010
-- Description: Returns the appropriate exchange rate
-- based on the input parameters.
-- If the rate cannot be found, returns NULL
-- (RAISEERROR can't be used in UDFs)
-- =============================================
ALTER FUNCTION [dbo].[GetExchangeRate]
(
@CurrencyFrom char(3),
@CurrencyTo char(3),
@OnDate date
)
RETURNS decimal(18,4)
AS
BEGIN
DECLARE @ClosingRate as decimal(18,4)
SELECT TOP 1
@ClosingRate=ClosingRate
FROM
[FactCurrencyRate]
WHERE
FromCurrencyCode=@CurrencyFrom AND
ToCurrencyCode=@CurrencyTo AND
DateID=dbo.DateToIntegerKey(@OnDate)
RETURN @ClosingRate
END
GO
回答by Mitch Wheat
RAISEERRORor @@ERRORare not allowed in UDFs. Can you turn the UDF into a strored procedure?
RAISEERROR或者@@ERROR在 UDF 中是不允许的。你能把 UDF 变成一个存储过程吗？
From Erland Sommarskog's article Error Handling in SQL Server – a Background:
来自 Erland Sommarskog 的文章SQL Server 中的错误处理 – 背景：
User-defined functions are usually invoked as part of a SET, SELECT, INSERT, UPDATE or DELETE statement. What I have found is that if an error appears in a multi-statement table-valued function or in a scalar function, the execution of the function is aborted immediately, and so is the statement the function is part of. Execution continues on the next line, unless the error aborted the batch. In either case, @@error is 0. Thus, there is no way to detect that an error occurred in a function from T-SQL.
The problem does not appear with inline table-functions, since an inline table-valued function is basically a macro that the query processor pastes into the query.
You can also execute scalar functions with the EXEC statement. In this case, execution continues if an error occurs (unless it is a batch-aborting error). @@error is set, and you can check the value of @@error within the function. It can be problematic to communicate the error to the caller though.
用户定义的函数通常作为 SET、SELECT、INSERT、UPDATE 或 DELETE 语句的一部分被调用。我发现，如果多语句表值函数或标量函数中出现错误，则函数的执行会立即中止，函数所属的语句也会中止。执行在下一行继续，除非错误中止了批处理。在任何一种情况下，@@error 都是 0。因此，无法从 T-SQL 检测到函数中发生了错误。
内联表函数不会出现该问题，因为内联表值函数基本上是查询处理器粘贴到查询中的宏。
您还可以使用 EXEC 语句执行标量函数。在这种情况下，如果发生错误，则继续执行（除非它是批处理中止错误）。@@error 已设置，您可以在函数内检查@@error 的值。但是，将错误传达给调用者可能会有问题。
回答by davec
The top answer is generally best, but does not work for inline table valued functions.
最佳答案通常是最好的，但不适用于内联表值函数。
MikeTeeVee gave a solution for this in his comment on the top answer, but it required use of an aggregate function like MAX, which did not work well for my circumstance.
MikeTeeVee 在他对最佳答案的评论中为此提供了一个解决方案，但它需要使用像 MAX 这样的聚合函数，这在我的情况下效果不佳。
I messed around with an alternate solution for the case where you need an inline table valued udf that returns something like select *instead of an aggregate. Sample code solving this particular case is below. As someone has already pointed out... "JEEZ wotta hack":) I welcome any better solution for this case!
在需要一个内联表值 udf 的情况下，我使用了一个替代解决方案，该 udf 返回类似select *而不是聚合的内容。解决这种特殊情况的示例代码如下。正如有人已经指出的那样...... “JEEZ wotta hack”:) 我欢迎任何更好的解决方案！
create table foo (
ID nvarchar(255),
Data nvarchar(255)
)
go
insert into foo (ID, Data) values ('Green Eggs', 'Ham')
go
create function dbo.GetFoo(@aID nvarchar(255)) returns table as return (
select *, 0 as CausesError from foo where ID = @aID
--error checking code is embedded within this union
--when the ID exists, this second selection is empty due to where clause at end
--when ID doesn't exist, invalid cast with case statement conditionally causes an error
--case statement is very hack-y, but this was the only way I could get the code to compile
--for an inline TVF
--simpler approaches were caught at compile time by SQL Server
union
select top 1 *, case
when ((select top 1 ID from foo where ID = @aID) = @aID) then 0
else 'Error in GetFoo() - ID "' + IsNull(@aID, 'null') + '" does not exist'
end
from foo where (not exists (select ID from foo where ID = @aID))
)
go
--this does not cause an error
select * from dbo.GetFoo('Green Eggs')
go
--this does cause an error
select * from dbo.GetFoo('Yellow Eggs')
go
drop function dbo.GetFoo
go
drop table foo
go
回答by NightShovel
A few folks were asking about raising errors in Table-Valued functions, since you can't use "RETURN [invalid cast]" sort of things. Assigning the invalid cast to a variable works just as well.
一些人询问在表值函数中引发错误，因为您不能使用“ RETURN [invalid cast]”之类的东西。将无效的强制转换分配给变量也同样有效。
CREATE FUNCTION fn()
RETURNS @T TABLE (Col CHAR)
AS
BEGIN
DECLARE @i INT = CAST('booooom!' AS INT)
RETURN
END
This results in:
这导致：
Msg 245, Level 16, State 1, Line 14 Conversion failed when converting the varchar value 'booooom!' to data type int.
消息 245，级别 16，状态 1，第 14 行转换 varchar 值 'booooom!' 时转换失败 到数据类型int。
回答by Michal Zglinski
I can't comment under davec's answer regarding table valued function, but in my humble opinion this is easier solution:
我无法在 davec 的关于表值函数的回答下发表评论，但在我看来，这是更简单的解决方案：
CREATE FUNCTION dbo.ufn_test (@a TINYINT)
RETURNS @returns TABLE(Column1 VARCHAR(10), Value1 TINYINT)
BEGIN
IF @a>50 -- if @a > 50 - raise an error
BEGIN
INSERT INTO @returns (Column1, Value1)
VALUES('error','@a is bigger than 50!') -- reminder Value1 should be TINYINT
END
INSERT INTO @returns (Column1, Value1)
VALUES('Something',@a)
RETURN;
END
SELECT Column1, Value1 FROM dbo.ufn_test(1) -- this is okay
SELECT Column1, Value1 FROM dbo.ufn_test(51) -- this will raise an error
回答by Alex
One way (a hack) is to have a function/stored procedure that performs an invalid action. For example, the following pseudo SQL
一种方法（黑客）是拥有一个执行无效操作的函数/存储过程。例如下面的伪SQL
create procedure throw_error ( in err_msg varchar(255))
begin
insert into tbl_throw_error (id, msg) values (null, err_msg);
insert into tbl_throw_error (id, msg) values (null, err_msg);
end;
Where on the table tbl_throw_error, there is a unique constraint on the column err_msg. A side-effect of this (at least on MySQL), is that the value of err_msg is used as the description of the exception when it gets back up into the application level exception object.
在表 tbl_throw_error 的何处，列 err_msg 有唯一约束。这样做的一个副作用（至少在 MySQL 上）是 err_msg 的值被用作异常的描述，当它返回到应用程序级异常对象时。
I don't know if you can do something similar with SQL Server, but worth a shot.
我不知道你是否可以用 SQL Server 做类似的事情，但值得一试。
