SQL参数值可以为null或可以不为null [英] Sql parameter value that may or may not be null

问题描述

我正在使用MS Sql Server2008.我的查询具有代码

I am using MS Sql Server 2008. My query has the code

WHERE RepCode = @RepCode

当@RepCode设置为非空值时，查询的行为与我期望的一样.当我将参数设置为DBNull.Value时，即使存在应返回的数据，查询也不会返回任何内容.如果我重写它

When @RepCode is set to a non-null value, the query behaves as I would expect. When I set the parameter to DBNull.Value, the query returns nothing, even though there is data that should be returned. If I rewrite it

WHERE RepCode IS @RepCode

那么该查询对于null值正确运行，但对于非null值则不正确.

我假设有某种写参数化查询的方法，其中参数可以为null，也可以不为null，这将始终返回正确的值.我该怎么办?

then the query works correctly for a null value but not for a non-null value.

I assume that there is some way to write a parameterized query where the parameter may or may not be null that will always return the correct value. How would I do this?

推荐答案

您已经发现问题的原因了. NULL永远不等于任何东西，甚至不等于另一个NULL.

一种解决方法是，通过用另一个值替换它来从比较中消除NULL.也许最简单的方法是使用 COALESCE [

You have pretty much discovered the reason for the problem. NULL is never equal to anything, not even to another NULL.

One workaround for this is that you eliminate NULL''s from the comparison by replacing it with another value. Perhaps the easiest way is to use COALESCE[^]. Using that, your example would be something like:

WHERE COALESCE(RepCode, -1) = COALESCE(@RepCode, -1)

因此，想法是将NULL替换为其他值.

重要提示:只需确保该值不存在于数据中，否则会导致错误的结果.

So the idea is that you replace the NULL with some other value.

Important: Just make sure that the value does not exist in the data since that would bring false results.

它对您没有特别的帮助，但MySQL具有<=>运算符，称为零安全等于"运算符或太空飞船"运算符.

我不认为T-SQL有这个:(

It doesn''t help you specifically, but MySQL has the <=> operator, called the "Null-safe equal-to" operator, or the "spaceship" operator.

I don''t think T-SQL has this :(

这篇关于SQL参数值可以为null或可以不为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！