我当前在尝试执行一个简单的SELECT
语句时收到错误,该语句引用包含 Jaro-Winkler 距离算法的 C# 代码的程序集。这是我第一次使用 SQLCLR。
我能够在没有额外的OR
语句的情况下成功运行下面的代码
例:
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
)
但是,当我包含OR
语句时,我收到此错误消息:
执行用户定义的例程或聚合"JaroWinkler"期间发生 .NET Framework 错误:
System.NullReferenceException:对象引用未设置为对象的实例。 System.NullReferenceException: at JaroWinklerDistanceCLR.JaroWinklerDistance.proximity(String aString1, String aString2(
下面的代码可以工作并执行所需的操作。我只是想知道是否有其他方法?理想情况下,我希望它包含在一个SELECT
声明中。我不知道上面的错误指的是什么,UserID
列中没有NULL
值。
程序集的权限集设置为"安全"。
工作示例:
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
另外两个答案是解决方法,而不是解决方案。并且必须在使用此函数的每个地方复制此解决方法,并且非常容易出错且难以维护。
在进入这里的主要问题之前,有一个次要的、相关的问题应该首先解决:输入参数类型不正确。对于 SQLCLR 方法,应使用Sql*
类型,而不是标准 .NET 类型。对于此特定代码,这意味着使用SqlString
而不是String
。有关为什么SqlString
而不是String
的更多详细信息,请参阅我对以下 SO 问题的回答:我应该使用 SqlString 还是字符串作为 SQLCLR UDF 的参数类型。
现在,问题是 SQLCLR 代码没有正确处理NULL
s.幸运的是,让它处理它们并不难。有两个选项,具体取决于任何输入参数是否可以接受NULL
:
-
如果任何输入参数可以在
NULL
中有效传递,则需要在代码中处理此问题(这也适用于使用表值函数和存储过程时的所有情况(。然后,您可以通过所有Sql*
类型都具有的.IsNull
属性签入代码。例如(假设aString2
可以传入NULL
(:if (aString1.IsNull) { return SqlDouble.Null; }
-
如果没有任何输入参数可以有效接受
NULL
,则应绕过所有处理,而无需首先输入代码,方法是使用CREATE FUNCTION
语句的WITH RETURNS NULL ON NULL INPUT
选项创建标量 UDF。设置此选项后,如果NULL
任何输入参数,则跳过代码并假定返回值NULL
。请注意,这仅适用于标量 UDF 和用户定义类型方法。
有关使用 SQLCLR的更多信息,请参阅我在 SQL Server Central 上撰写的有关此主题的系列文章(需要免费注册才能阅读该站点上的内容(:通往 SQLCLR 的阶梯。
在相关的说明中,我会质疑使用字符串距离函数来执行相当简单的日期计算。我认为将 JaroWinkler 函数替换为基于将字符串@DOB
转换为DATE
或DATETIME
的DATEDIFF
将极大地受益。
OR 语句最有可能将 NULL 值包含在结果集中,这些值作为不同的数据类型返回。尝试使用
OR (USERID = @USERID AND AND P.DATE_OF_BIRTH IS NOT NULL)
或者,如果需要 NULL 值,则显式选择字段名称(而不是选择 *(并将date_of_birth字段括在转换语句中
谢谢。NULL
出生日期字段中的值。
加工!
SELECT
*
FROM
USERS
WHERE
(
DBO.JAROWINKLER(CONVERT(VARCHAR(6),ISNULL(P.DATE_OF_BIRTH,''),12),@DOB) > 0.9
)
OR
(
USERID = @USERID
)