如何报告来自SQL Server用户定义函数的错误

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，但是对于任何使用该函数进行故障排除的开发人员来说都很困难。我也可以导致一个0的除法或者类似的东西——这会产生一个错误消息，但是是一个误导的消息。有什么方法可以报告我自己的错误消息吗?

9 个解决方案

#1

187

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.

#2

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.

通常的技巧是把a除以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。

#3

Following on from Vladimir Korolev's answer, the idiom to conditionally throw an error is

根据弗拉迪米尔·科罗洛夫的回答，有条件抛出错误的习语是

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)

#4

RAISEERROR or @@ERROR are not allowed in UDFs. Can you turn the UDF into a strored procedure?

在udf中不允许使用RAISEERROR或@@ERROR。你能把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.

用户定义函数通常作为集合、选择、插入、更新或删除语句的一部分进行调用。我所发现的是，如果一个错误出现在多语句表值函数或标量函数中，那么函数的执行就会立即终止，而函数的语句也是其中一部分。执行继续在下一行，除非错误中止了批处理。无论哪种情况，@@error都是0。因此，无法检测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.

您还可以使用EXEC语句执行标量函数。在这种情况下，如果发生错误(除非是批处理错误)，则继续执行。设置了@@error，可以在函数中检查@@error的值。但是，将错误传递给调用者可能会有问题。

#5

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

#6

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(返回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

#7

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.

Msg 245，第16级，状态1，第14行转换失败，当转换varchar值'booooom!的数据类型。

#8

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

#9

-3

One way (a hack) is to have a function/stored procedure that performs an invalid action. For example, the following pseudo SQL

一种方法(hack)是拥有一个执行无效操作的函数/存储过程。例如，下面的伪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做类似的事情，但值得一试。

#1

187