SQL Server - handling null input in user-defined CLR function (UDF) using OnNullCall

Question

I have a custom function in SQL Server (written by .NET) that clears text. I am 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 an error in SQL if the function receives 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 the input argument values ​​are null (Nothing in Visual Basic); false if the method returns null (Nothing in Visual Basic) when any of its input parameters is null (Nothing in Visual Basic).

It sounds exactly the way I want; if i get null just skip null. I'm not quite sure how to implement it, so I check the MSDN again (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattrib.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 anytime 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 do something else? Is there any good way to get my function to pass null through?

Answer 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 the IsNull property.

Thanks Hari

Answer 2

The accepted answer is incorrect, although it technically works. The problem with the NULL check in the code itself is that the code is called and must perform this check. This is only necessary if it is required that one or more parameters pass a valid NULL without causing the method to be skipped.

This can certainly be done, but, unfortunately, not through the Visual Studio / SSDT publishing mechanism that creates all of T-SQL for you. To do this, you need to either:

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

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

To express this 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 parameter is specified, then any input parameter NULL will skip the function and return NULL .

UPDATE:
Please vote on the following Microsoft Connect offer, so hopefully support is added for the OnNullCall property of the OnNullCall attribute:

Embed the OnNullCall property in SqlFunctionAttribute for RETURNS NULL ON NULL INPUT

Answer 3

@Josh, what does it cost, I call my CLR functions, wrapping all the parameters with the coalesce function. So something like select myFunc(coalesce(fld1,'')) . Then in my CLR function, I check the parameter values ​​at the very top, something like if (param1.ToString() == '') return SqlString.Null . Of course, you can do everything you need inside the function, but the general template that I used to get around the zero error using CLR procedures / functions. It is a hassle to remember to wrap them every time I use them, but it works.

Answer 4

Edit: This was written before the answer above. Look at this.

I'm still sure there is 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 get a workaround, I did a pretty obvious thing: mark zeros.

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