SQL Server -- Handling null input in CLR User-Defined Function (UDF) with OnNullCall

Question

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

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.

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

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

[Microsoft.SqlServer.Server.SqlFunction]

to

[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:

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?

Hari Sandeep Gutta · Accepted Answer · 2011-04-08T21:20:49.140

9

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

edited Apr 08 '11 at 21:20

answered Apr 08 '11 at 21:10

Hari Sandeep Gutta

106
1
4

The above two solutions are simply work-arounds that do not address the issue. Hari's answer is the correct one. – alphadogg Oct 25 '11 at 15:28
@alphadogg and Hari, this actually is _not_ the correct answer. At least not in the context of the question. It is only the correct answer when there are multiple input parameters and at least one of them can validly pass in a `NULL` value. Otherwise you should use `WITH RETURNS NULL ON NULL INPUT` in the `CREATE FUNCTION` as described in my answer. – Solomon Rutzky Feb 25 '15 at 20:07
`if (input.IsNull) return null;` -- short and simple! Works when return type is `string` too, instead of `SqlChars`. – James L. Aug 14 '19 at 18:15

Solomon Rutzky · Answer 2 · 2018-03-21T17:19:07.817

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

score 1 · Answer 3 · answered Mar 19 '11 at 07:07

1

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

answered Mar 19 '11 at 07:07

sisdog

2,649
2
29
49

That seems pretty useful. The only issue is that Null and '' may need to be differentiated. However, I think in my case changing empty strings to Null would work just as well. Thanks. – jtpereyda Mar 21 '11 at 16:04
2

@EvilDr This is actually a bad, convoluted, and unnecessary pattern. If you need to allow `NULL` values into your code for at least one input param, then you can simply test any/all of the input params using the `IsNull` property that all of the SqlTypes have. If NONE of the input params ever pass in a NULL, then you can skip the method call in the first place by specifying `WITH RETURNS NULL ON NULL INPUT`. Please see my [answer](http://stackoverflow.com/a/28728440/577765) for details. – Solomon Rutzky Jun 04 '15 at 15:53
1

Actually yes I agree. After more reading and consideration the answer regarding `IF value.IsNull` combined with `If String.IsNullOrWhitespace` was perfect. Your overall input on each point here is outstanding. – EvilDr Jun 04 '15 at 19:37

jtpereyda · Answer 4 · 2012-07-20T17:21:32.263

-1

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

edited Jul 20 '12 at 17:21

answered Jan 20 '11 at 20:23

jtpereyda

6,987
10
51
80

SQL Server -- Handling null input in CLR User-Defined Function (UDF) with OnNullCall

4 Answers4

Linked