为什么 UDF 中不允许使用 TRY-CATCH 块? [英] Why is TRY-CATCH block not allowed inside UDFs?

问题描述

为什么 SQL Server 不支持 UDF 中的 TRY-CATCH 块?

Why doesn't SQL Server support TRY-CATCH blocks inside UDFs?

如果我们谈论的是标量 UDF，它主要用于计算和对话，这个块应该被大量使用，但我们没有它.

If we're talking about scalar UDFs, which are mostly used for calculations and conversations, this block should be heavily used, but we don't have it.

此外，您对此使用了哪些解决方法?

Also, what workarounds do you use for this?

推荐答案

MSSQL 中的 UDF 不允许有副作用，BOL 将其定义为更改数据库状态".这是一个相当模糊的描述，但 MSSQL 显然认为错误会改变数据库状态 - 这个 UDF 无法编译:

UDFs in MSSQL are not allowed to have side effects, which BOL defines as "changing the database state". That's a rather vague description, but MSSQL apparently considers errors to change the database state - this UDF doesn't compile:

create function dbo.foo()
returns int
as
begin
    raiserror('Foo', 16, 1)
    return 1
end
go

错误信息是:

消息 443，级别 16，状态 14，程序foo, Line 5 Invalid use of a副作用运算符RAISERROR"在一个函数内.

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

如果认为引发错误会改变数据库状态，那么捕获和处理一个错误可能也是如此.我承认，这没什么好解释的.

If raising an error is considered to change the database state, then trapping and handling one presumably is too. Which is not much of an explanation, I admit.

不过，实际上，通常最好让调用者决定如何处理错误.假设你写了一个这样的函数:

In practical terms, though, it's often best to let the caller decide how to handle errors anyway. Say you write a function like this:

create function dbo.divide (@x int, @y int)
returns float
as
begin
return @x / cast(@y as float)
end

您将如何处理应用程序为@y 传递零的情况?如果您捕获除以零异常，您接下来要做什么?你可以从对调用者有意义的函数返回什么值，记住你甚至可能不知道哪个应用程序正在调用你的函数?

How would you handle the case where an application passes zero for @y? If you catch the divide by zero exception, what are you going to do next? What value can you return from the function that makes sense to the caller, bearing in mind that you may not even know which application is calling your function anyway?

您可能会考虑返回 NULL，但是使用您的函数的应用程序开发人员会同意吗?他们的所有应用程序是否都认为除以零错误具有相同的影响或重要性?更不用说某些地方的 NULL 可以完全改变查询的结果，也许是应用程序开发人员根本不想要的方式.

You might think of returning NULL, but would the application developers using your function agree? Do all their applications consider a divide by zero error to have the same impact or importance? Not to mention that NULLs in certain places can completely change the results of a query, perhaps in ways that the application developer doesn't want at all.

如果您是唯一的开发人员，也许这不是问题，但随着人数的增加，问题很快就会解决.

If you're the only developer, maybe it isn't an issue, but with more people it quickly becomes one.

这篇关于为什么 UDF 中不允许使用 TRY-CATCH 块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！