如何从 SQL Server 用户定义函数报告错误 [英] How to report an error from a SQL Server user-defined function

问题描述

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

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:

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?

我当然可以返回 NULL，但是任何使用该函数的开发人员都很难解决这个问题.我也可能导致除以零或类似的东西 - 这会产生一条错误消息，但会产生误导.有什么办法可以让我以某种方式报告我自己的错误消息?

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?

推荐答案

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

You can use CAST to throw meaningful error:

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

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

Then Sql Server will show some help information:

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

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