Sqlclr .net Error: Object Reference Not Set To An Instance Of An Object

I am currently receiving an error when trying to execute a simple SELECT statement that references an assembly that contains the C# code for the Jaro-Winkler distance algorithm. Th

Solution 1:

The other two answers are work arounds, not solutions. And this work-around will have to be duplicated in every place where this function is used, and that is very error-prone and difficult to maintain.

Before getting into the main problem here, there is a minor, related problem that should be fixed first: the input parameter types are incorrect. For SQLCLR methods, you should use the Sql* types instead of the standard .NET types. For this particular code, that means using SqlString instead of String. For more details on why SqlString instead of String, please see my answer to the following S.O. question: Should I use SqlString or string as parameter type to SQLCLR UDF's.

Now, the problem is that the SQLCLR code is not properly handling NULLs. Fortunately, it is not difficult to get it to handle them. There are two options, depending on if any of the input parameters can accept a NULL or not:

• If any of the input parameters can validly pass in a NULL, then you need to handle this in the code (and this also applies to all cases when working with Table-Valued Functions and Stored Procedures). And you check in the code via the .IsNull property that all of the Sql* types have. For example (assuming aString2 can pass in a NULL):

  if (aString1.IsNull)
  {
    return SqlDouble.Null;
  }
  

• If none of the input parameters can validly accept NULL, then you should bypass all processing without entering the code in the first place by creating the Scalar UDF with the WITH RETURNS NULL ON NULL INPUT option of the CREATE FUNCTION statement. With this option set, if any input parameter is NULL, then the code is skipped and a NULL return value is assumed. Please note that this only works with Scalar UDFs and User-Defined Type methods.

For more information on working with SQLCLR in general, please see the series of articles I am writing on this topic on SQL Server Central (free registration is required to read content on that site): Stairway to SQLCLR.

---

On a related note, I would question the use of a string distance function to do what is a rather simple date calculation. I would think that this code would benefit greatly from replacing the JaroWinkler function with a DATEDIFF based on converting the string @DOB to DATE or DATETIME.

Solution 2:

The OR statement is most likely including NULL values into your result set which are returning as a different data type. Try using

OR (USERID = @USERID AND AND P.DATE_OF_BIRTH IS NOT NULL)

or, if you require the NULL values then select the field names explicitly (rather than select *) and wrap the date_of_birth field in a convert statement

Solution 3:

Thank you. NULL values in the date of birth field.

Working!

SELECT
    * 
FROMUSERSWHERE
(
    DBO.JAROWINKLER(CONVERT(VARCHAR(6),ISNULL(P.DATE_OF_BIRTH,''),12),@DOB) > 0.9
)
OR
(
    USERID = @USERID
)