SQL Server - 使用OnNullCall处理CLR用户定义函数（UDF）中的空输入

I have a user-defined function in SQL Server (written in .NET) that cleans text. I'm wondering how to handle null input.

我在SQL Server(用.NET编写)中有一个用户定义的函数来清理文本。我想知道如何处理空输入。

Here is the function in C#:

这是C#中的函数:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlChars cleanEstActText(SqlChars input)
{
    SqlChars cascadingSqlChar = removeNBSP(input);
    cascadingSqlChar = optimizeFontTags(cascadingSqlChar);

    return cascadingSqlChar;
}

This is the error in SQL if the function gets any null data:

如果函数获取任何空数据,则这是SQL中的错误:

A .NET Framework error occurred during execution of user-defined routine or aggregate "removeNBSP": 
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException: 
   at System.Data.SqlTypes.SqlChars.get_Value()
   at UserDefinedFunctions.removeNBSP(SqlChars input)

Reading on SO and Google led me to the OnNullCall attribute, which looks promising.

阅读SO和Google让我看到了OnNullCall属性,这看起来很有希望。

From MSDN:

true if the method is called when null (Nothing in Visual Basic) input arguments are specified in the method invocation; false if the method returns a null (Nothing in Visual Basic) value when any of its input parameters are null (Nothing in Visual Basic).

如果在方法调用中指定null(在Visual Basic中为Nothing)输入参数时调用该方法,则为true;否则为false。如果方法在其任何输入参数为null时返回null(在Visual Basic中为Nothing)值,则返回false(在Visual Basic中为Nothing)。

Sounds exactly like what I want; if I get null, just pass null through. I'm not quite sure how to implement it, so I check MSDN again (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattribute.aspx), and rewrite the first line of my function from

听起来和我想要的完全一样;如果我得到null,只需传递null。我不太确定如何实现它,所以我再次检查MSDN(http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattribute.aspx),并重写第一行我的功能来自

[Microsoft.SqlServer.Server.SqlFunction]

[Microsoft.SqlServer.Server.SqlMethod(OnNullCall = false, IsMutator = false, InvokeIfReceiverIsNull = false)]

If I do this, I get an error in SQL any time I use it:

如果我这样做,我在使用它时会在SQL中出错:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.cleanEstActText", or the name is ambiguous.

Am I implementing OnNullCall incorrectly? Should I be doing something else? Is there really any good way to make my function pass nulls through?

我是否错误地实施了OnNullCall?我应该做别的吗?有没有什么好办法让我的函数通过null?

4 个解决方案

#1

You can try this

你可以试试这个

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlChars cleanEstActText(SqlChars input)
{

    if (input.IsNull) return null;

    SqlChars cascadingSqlChar = removeNBSP(input);
    cascadingSqlChar = optimizeFontTags(cascadingSqlChar);

    return cascadingSqlChar;
}

All Nullable SqlData Types have an IsNull Propery.

所有Nullable SqlData类型都有一个IsNull Propery。

Thanks Hari

#2

The accepted answer is not correct, though it does technically work. The problem with checking for NULLs in the code itself is that the code is called and has to perform that check. This is required only when wanting to allow one or more parameters to pass a valid NULL in without causing the execution of the method to be skipped.

接受的答案是不正确的,尽管它在技术上有效。在代码本身中检查NULL的问题是代码被调用并且必须执行该检查。仅当希望允许一个或多个参数传递有效NULL而不导致跳过执行方法时才需要这样做。

This is definitely possible to do, though unfortunately not through the Visual Studio / SSDT publishing mechanism that creates all of the T-SQL for you. In order to accomplish this, you need to either:

这绝对是可以做到的,但遗憾的是,不是通过Visual Studio / SSDT发布机制为您创建所有T-SQL。为了实现这一目标,您需要:

Manually deploy the T-SQL CREATE FUNCTION statement

手动部署T-SQL CREATE FUNCTION语句

Do an ALTER FUNCTION after the code has been published to SQL Server

代码发布到SQL Server后执行ALTER FUNCTION

In either case, the syntax for this, as described in the MSDN page for CREATE FUNCTION, is: WITH RETURNS NULL ON NULL INPUT.

在任何一种情况下,如CREATE FUNCTION的MSDN页面中所述,其语法为:WITH RETURNS NULL ON NULL INPUT。

To put it in full context:

把它放在完整的上下文中:

CREATE FUNCTION SchemaName.FunctionName ( { parameter_list } )
RETURNS DataType
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME ...

Again, keep in mind that if this option is specified, then any input parameter that is NULL will cause the function to be skipped and return NULL.

同样,请记住,如果指定了此选项,则任何NULL的输入参数都将导致跳过该函数并返回NULL。

UPDATE:
Please vote on the following Microsoft Connect suggestion so that hopefully support is added for the OnNullCall property of the SqlFunction attribute:

更新:请对以下Microsoft Connect建议进行投票,以便希望为SqlFunction属性的OnNullCall属性添加支持:

Implement OnNullCall property in SqlFunctionAttribute for RETURNS NULL ON NULL INPUT

在SqlFunctionAttribute中为RETURNS实现OnNullCall属性NULL ON INPUT

#3

@Josh, for what it's worth I call my CLR functions by wrapping all the params with a coalesce function. So, something like select myFunc(coalesce(fld1,'')). Then, in my CLR function I check the values of the params at the very top, something like if (param1.ToString() == '') return SqlString.Null. Of course, you can do whatever you you need to inside the function but that's the general pattern I've been using to get around the null issue with CLR procs/functions. It's a hassle to remember to wrap them every time I use them but it works.

@Josh,因为我需要通过使用合并函数包装所有参数来调用我的CLR函数。所以,比如选择myFunc(coalesce(fld1,''))。然后,在我的CLR函数中,我检查最顶部的参数值,如if(param1.ToString()=='')返回SqlString.Null。当然,你可以在函数内部做任何你需要的东西,但这是我用来解决CLR procs / functions的null问题的一般模式。每次使用它们时都记得将它们包起来很麻烦,但它有效。

#4

Edit: This was written before the now-accepted answer above. See that one instead.

编辑:这是在上面现在接受的答案之前写的。请看那个。

I'm still convinced there's a way to do this, but I haven't found it (I don't know enough about how SQL interacts with the CLR anyway).

我仍然相信有一种方法可以做到这一点,但我还没有找到它(我还不知道SQL如何与CLR交互)。

To workaround, I did the fairly obvious thing: check for nulls.

为了解决方法,我做了相当明显的事情:检查空值。

Select dbo.cleanEstActText(EstActText1)
From BLEstActivity
Where EstActText1 is not NULL

#1