Data is Null Error with SQL CLR Function

Question

I am trying to implement a basic SQL - VB CLR function which can check a list of values against a current value to test if the current value is included. (Mainly as a test of how to do CLR functions.) I have created the Dll, Loaded it to the database as assembly, create the function SQL and when I call it, I receive the following error. See below for the Error, SQL Function & Visual Basic Class. Note, 1 prior version of this function worked on the database a few times, but once I recieved this error, it gives me this same error every time now.

I have searched online and have found several posts which list the same problem, but none have a solution which works. The Compile has no errors, the assembly has no errors, database is trusted, CLR is enabled on the database. I just can't seem to figure out how to fix this issue. I have tried to implement null checking in the function, but the error seems to be occurring during the pass from SQL to the function itself, not within the function, so I thought it may be a datatype issue, but I am using SQL datatypes for both import and export... I do use vb data types within, but my null check should handle if there is any null data. Please Help!!!

Error Message:

Msg 6522, Level 16, State 2, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "fnCompareF": 
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.SqlString.get_Value()
   at Common.JBM_CommonSQL.fnCompareF(SqlString strList, SqlString strCurrentValue)

SQL Function:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION dbo.fnCompareF (@List [nvarchar](4000), @CompareTo [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME CommonSQL.[Common.JBM_CommonSQL].fnCompareF

VB 2013 Class:

Option Explicit On
Imports System.Collections
Imports System.Text
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Class JBM_CommonSQL

    <SqlFunction>
    Public Shared Function fnCompareF(strList As SqlString, strCurrentValue As SqlString) As SqlBoolean

        '----------------------------------------------------------------------------------------------------------------------------------------------------
        'THIS FUNCTION COMPARES A CURRENT VALUE TO A STRING, CONVERTS TO ARRAY
        'AND RETURNS TRUE IF THE ITEM IS FOUND IN THE ARRAY
        'strCompareList() = THIS IS EXPECTING ANY SINGLE DIMENSIONAL ARRAY (USUALLY STRING VALUES WHICH SHOULD BE COMPARED TO THE CURRENT VALUE
        '                   EVERY VALUE HERE IS COMPARED AGAINST THE strCurrentValue
        'strCurrentValue  = ANY STRING WHICH YOU WISH TO KNOW IS IN OR NOT IN THE ARRAY ABOVE
        '----------------------------------------------------------------------------------------------------------------------------------------------------
        Dim x As Long, strCompareList() As String, strDelim As String
If strList.IsNull Or strCurrentValue.IsNull Then fnCompareF = 0 : Exit Function
strDelim = fnFindDelim(strList)
        If strDelim = "" Then strDelim = ","
        strCompareList = Split(strList, strDelim)
        For x = LBound(strCompareList) To UBound(strCompareList)
            If UCase(strCompareList(x)) = UCase(CStr(strCurrentValue)) Then
                fnCompareF = 1
                Exit Function
            End If
        Next x
        fnCompareF = 0
    End Function

<SqlFunction> _
Public Shared Function fnFindDelim(strList As SqlString) As SqlString
    Dim strDelims() As String, x As Long, lngMax As Long, lngCount(2) As Long
    Dim strClean As String
    strDelims = Split("|#DEL#,#DEL#^#DEL#;", "#DEL#")
    For x = LBound(strDelims) To UBound(strDelims)
        lngCount(0) = Len(CType(strList, String))
        strClean = Replace(CType(strList, String), strDelims(x), "#DEL#")
        lngCount(1) = Len(strClean)
        lngCount(2) = (lngCount(1) - lngCount(0)) / 5
        If lngMax < lngCount(2) Then
            fnFindDelim = strDelims(x)
            lngMax = lngCount(2)
        End If

    Next x
End Function

End Class

SQL CODE CALLING THIS FUNCTION:

SELECT * FROM  dbo.WorkFlowMembers WHERE  DBO.fnCompareF(WorkFlowRoles, '1') = 1

Workflow members contains 2 columns, StaffID varchar(8), WorkflowRoles Varchar(50)

Workflow roles is a string usually containing a list of Role ID's IE( "1,2,3,4,5,6" )

I know the function parameters are for Nvarchar, however I have tried the same using cast( as nvarchar) and got the same error message. If I hardcode the Parameters DBO.fnCompareF('1,2,3,4,5', '1') = 1, The function works and returns a valid list.

I found the following question, and have tried the solution presented, but this did not work. SQL Server -- Handling null input in CLR User-Defined Function (UDF) with OnNullCall

Data is Null Error with SQL CLR Function

0 Answers0