SQL Server -- Handling Null Input In CLR User-Defined Function (UDF) With OnNullCall

I have a user-defined function in SQL Server (written in .NET) that cleans text. I'm wondering how to handle null input. Here is the function in C#: [Microsoft.SqlServer.Server.Sq

Solution 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.

Thanks Hari

Solution 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.

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:

• Manually deploy the T-SQL CREATE FUNCTION statement
• Do an ALTER FUNCTION after the code has been published to SQL Server

In either case, the syntax for this, as described in the MSDN page for CREATE FUNCTION, is: 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.

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

Implement OnNullCall property in SqlFunctionAttribute for RETURNS NULL ON NULL INPUT

Solution 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.

Solution 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).

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

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