SQL Server：如果不是Null则检查输入参数并在其中使用它？

what is the best way to include an input param in the WHERE clause but exclude it if it is null?

在WHERE子句中包含输入参数的最佳方法是什么，但如果它为null则将其排除？

There are a number of ways I believe but I can't seem to remember then.

我相信有很多方法，但我似乎无法记住。

Also could I use the COALESCE() ?? But I think this is only for SELECTing values?

我也可以使用COALESCE（）??但我认为这仅适用于SELECTing值？

Any help really appreciated

任何帮助真的很感激

EDIT

编辑

To clarify

澄清

lets say a variable called @code ="1" then my where would be "Where type='B' AND code = @code" but if @code is null then i only want "Where type='B'" - notice the missing code = @code

让我们说一个名为@code =“1”的变量然后我的位置是“Where type ='B'AND code = @code”但是如果@code为null那么我只想要“Where type ='B'” - 注意丢失代码= @code

7 个解决方案

#1

You can use IsNull

您可以使用IsNull

 where some_column = IsNull(@yourvariable, 'valueifnull')

EDIT:

编辑：

What you described in the comment can be done like:

您在评论中描述的内容可以像：

where (@code is null or code = @code)

#2

How about

怎么样

WHERE (Column1 = @Var1 OR @Var1 IS NULL)
AND (Column2 = @Var2 OR @Var2 IS NULL)

#3

Here's another approach

这是另一种方法

SELECT * FROM Thingies WHERE ( @thingId IS NULL OR ThingID = @thingId )

If your parameter is NULL, the rest of the OR expression won't be evaluated.

如果参数为NULL，则不会计算OR表达式的其余部分。

#4

check out this neat article here. It explains why "where (@param is null or Field=@param)" doesn't perform well and what to use instead.

看看这篇简洁的文章。它解释了为什么“where（@param为null或Field = @param）”表现不佳以及使用什么。

#5

I’d like to suggest a solution which I found on another site:

我想建议一个我在另一个网站上找到的解决方案：

SELECT * FROM Thingies 
WHERE ThingID = isnull(@ThingId,ThingID)

With this solution if the user selects null for your parameter then your query will return all the rows as the result.

使用此解决方案，如果用户为您的参数选择null，那么您的查询将返回所有行作为结果。

#6

This question really helped me with a similar issue that had a few of us scratching our heads for a bit. I only write it up in case somebody else tries the same approach and cannot figure out why it does not work.

这个问题真的帮助了我一个类似的问题，让我们中的一些人摸不着头脑。我只写了以防其他人尝试相同的方法，但无法弄清楚为什么它不起作用。

I was trying to only evaluate a part of a multipart WHERE clause if the @Parameter was not null. I tried to do this as below but always had no rows returned if @Parameter was null.

如果@Parameter不为null，我试图仅评估多部分WHERE子句的一部分。我尝试按下面的方式执行此操作，但如果@Parameter为null，则始终没有返回任何行。

DECLARE @Parameter int = null;

SELECT  *  FROM  TABLE
WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter)

I incorrectly thought that (@Parameter is not null AND [AlternateID] = @Parameter) would simply not form part of the full WHERE clause is @Parameter was null. However it was making the entire WHERE clause return false. The remedy was to add an OR 1=1 as below:

我错误地认为（@Parameter不为空并且[AlternateID] = @Parameter）根本不构成完整WHERE子句的一部分是@Parameter为null。但是它使整个WHERE子句返回false。补救措施是添加OR 1 = 1，如下所示：

WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter OR 1=1)

Of course the approach outlined by Ali (not enough reputation to upvote) solves this more efficiently.

当然，阿里概述的方法（没有足够的声誉来提升）可以更有效地解决这个问题。

WHERE   [AlternateID] is not null 
        AND [Partner_Customer_ID] = ISNULL(@Parameter, [Partner_Customer_ID])

#7

You can use ISNULL(), or check for nulls explicitly as others have mentioned. This should be OK as long as you have no more than 1 or 2 optional input parameters. But if there are more parameters, this approach would be very inefficient as the indexes you create on those columns won't be used as you would expect. In such a case i would recommend you to use dynamic SQL. Here is an excellent article that explains why http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

您可以使用ISNULL（），或者像其他人提到的那样显式检查空值。只要您有不超过1或2个可选输入参数，这应该没问题。但是如果有更多参数，这种方法效率非常低，因为您在这些列上创建的索引将不会像您期望的那样使用。在这种情况下，我建议您使用动态SQL。这是一篇很好的文章，解释了为什么http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

#1