Zakerias Zakerias - 1 year ago 44
SQL Question

SQL CLR .NET Error

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download