如何从 SQLServer 用户定义函数报告错误

我正在 SQLServer2008中编写一个用户定义函数。我知道函数不能以通常的方式引发错误-如果你尝试包含 RAISERROR 语句 SQL 返回:

Msg 443, Level 16, State 14, Procedure ..., Line ...
Invalid use of a side-effecting operator 'RAISERROR' within a function.

但事实是，该函数接受一些输入，这些输入可能是无效的，如果是无效的，则该函数不能返回任何有意义的值。那我该怎么办？

当然，我可以返回 NULL，但是对于任何使用该函数进行故障排除的开发人员来说都是困难的。我也可以导致除以零或类似的东西-这将产生一个错误消息，但误导之一。有没有什么方法可以报告我自己的错误消息？

77416 次浏览
小开

在 UDF 中不允许使用 RAISEERROR或 @@ERROR。可以将 UDF 转换为字符串过程吗？

来自 Erland Sommarskog 的文章 SQLServer 中的错误处理-后台:

用户定义的函数通常是 作为 SET SELECT 的一部分调用, INSERT、 UPDATE 或 DELETE 语句。 我发现如果一个错误 出现在多个语句中 表值函数或在标量中 函数，执行 函数立即中止，并且 函数的语句也是如此 部分。继续执行 下一行，除非错误中止 在任何一种情况下,@@ error 都是 0. 因此，无法检测函数中是否发生了错误 来自 T-SQL。

问题没有出现 内联表函数，因为 内联表值函数是 基本上是一个宏，查询 处理器粘贴到查询中。

还可以执行标量函数 EXEC 语句。在这种情况下, 如果发生错误，则继续执行 (除非是批中止错误)。 @@error 已设置，您可以检查 函数中@@ error 的值。 沟通是有问题的 调用者的错误。

小开

一种方法是使用一个执行无效操作的函数/存储过程。例如，下面的伪 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;

在表 tbl _ throw _ error 上，对 err _ msg 列有一个唯一的约束。这样做的一个副作用(至少在 MySQL 上)是，当异常返回到应用程序级异常对象时，err _ msg 的值被用作异常的描述。

我不知道您是否可以对 SQLServer 进行类似的操作，但是值得一试。

小开

通常的技巧是强制除以0。这将引发一个错误并中断正在计算函数的当前语句。如果开发人员或支持人员知道这种行为，那么调查和故障排除问题就相当容易，因为除以0的错误被理解为一个不同的、不相关的问题的症状。

从任何角度来看，这都很糟糕，但不幸的是，目前 SQL 函数的设计没有更好的选择。在函数中应该绝对允许使用 RAISERROR。

小开

我认为最干净的方法是接受如果传递了无效参数，函数可以返回 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
小开
最佳答案

您可以使用 CAST 抛出有意义的错误:

create function dbo.throwError()
returns nvarchar(max)
as
begin
return cast('Error happened here.' as int);
end

然后 Sql Server 将显示一些帮助信息:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error happened here.' to data type int.
小开

接下来是弗拉基米尔 · 科罗廖夫的回答，有条件地抛出一个错误的成语是

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)
小开

顶部的答案通常是最好的，但不适用于内联表值函数。

MikeTeeVee 在他关于顶级答案的评论中给出了一个解决方案，但是它需要使用像 MAX 这样的聚合函数，这在我的环境中并不能很好地工作。

对于需要一个内联表值 udf 来返回类似 < em > 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
小开

我不能评论 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
小开

一些人询问表值函数中的错误，因为您不能使用“ 返回[无效强制转换]”之类的东西。将无效强制转换分配给变量也同样有效。

CREATE FUNCTION fn()
RETURNS @T TABLE (Col CHAR)
AS
BEGIN


DECLARE @i INT = CAST('booooom!' AS INT)


RETURN


END

结果是:

味精245,16层，州1,14线 当将 varchar 值‘ booooom!’转换为数据类型 int 时，转换失败。


提交答案