SQLCLR .NET Error: Object reference not set to an instance of an object

Question

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. This is my first time working with SQLCLRs.

I am able to run the code successfully below without the additional OR statement

Example:

SELECT
    * 
FROM
    USERS
WHERE
(
    DATE_OF_BIRTH IS NOT NULL 
    AND DBO.JAROWINKLER(CONVERT(VARCHAR(6),DATE_OF_BIRTH,12),@DOB) > 0.9
)
OR
(
    USERID = @USERID
)

However when I include the OR statement I receive this error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "JaroWinkler":

System.NullReferenceException: Object reference not set to an instance of an object. System.NullReferenceException: at JaroWinklerDistanceCLR.JaroWinklerDistance.proximity(String aString1, String aString2)

The code below works and does what is needed. I just wondered if there was another way? Ideally, I would like it contained to one SELECT statement. I have no idea what the error above is referring to, there are no NULL values in the UserID column.

The permission set for the assembly is set to Safe.

Working example:

SELECT
    *
FROM
    USERS
WHERE
    DATE_OF_BIRTH IS NOT NULL 
    AND DBO.JAROWINKLER(CONVERT(VARCHAR(6),DATE_OF_BIRTH,12),@DOB) > 0.9

UNION ALL

SELECT
    *
FROM
    USERS
WHERE
    USERID = @USERID

So are you the author of the JAROWINKLER and asking how to fix the null reference exception? — Alex K.
– Alex K., Commented Jul 27, 2017 at 14:01
@AlexK. No I am passing values to the distance algorithm. My problem is the "bolt-on" OR statement in SQL. en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance — Zakerias
– Zakerias, Commented Jul 27, 2017 at 14:33

Solomon Rutzky · Accepted Answer · 2017-07-28 20:27:08Z

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.

edited Jul 28, 2017 at 20:27

answered Jul 27, 2017 at 18:57

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zakerias Over a year ago

Thank you, Solomon, this is extremely helpful. I will be implementing your suggestions.

Solomon Rutzky Over a year ago

@Zakerias You're welcome :-). And I just added a link to my answer on a question (also on S.O.) specifically about SqlString vs String.

Richard Taylor · Accepted Answer · 2017-07-27 14:53:56Z

1

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

answered Jul 27, 2017 at 14:53

Richard Taylor

311 silver badge3 bronze badges

Comments

Zakerias · Accepted Answer · 2017-07-27 15:04:49Z

0

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

Working!

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

answered Jul 27, 2017 at 15:04

Zakerias

3562 gold badges3 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQLCLR .NET Error: Object reference not set to an instance of an object

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related