忽略T-SQL中的NULL参数

I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it.

我希望能够传入一个参数列表,并忽略那些为NULL的参数。这样查询实际上假装过滤器不存在并忽略它。

I was doing it like this:

我是这样做的:

(@thing IS NULL or Thing=@thing)

Is this right, and if so, would it perform badly? It's seems to be a lot slower than constructing the SQL separately.

这是对的,如果是的话,它会表现得很糟糕吗?它似乎比分别构造SQL要慢很多。

What's the optimal way to do this?

这样做的最佳方法是什么?

FIXED! See Marc Gravell's answer. In summary using IS NULL many times is a big performance hit.

固定!请参阅Marc Gravell的回答。总之,使用IS NULL多次是一个很大的性能影响。

9 个解决方案

#1

Once you get more than a couple of these, then yes: it starts to get pretty slow. In such cases, I tend to use generated TSQL - i.e.

一旦你获得了超过其中的几个,那么是的:它开始变得非常缓慢。在这种情况下,我倾向于使用生成的TSQL - 即

DECLARE @sql nvarchar(4000)
SET @sql = /* core query */

IF @name IS NOT NULL
    SET @sql = @sql + ' AND foo.Name = @name'

IF @dob IS NOT NULL
    SET @sql = @sql + ' AND foo.DOB = @dob'

// etc

EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime',
        @name, @dob

etc

Note that sp_ExecuteSQL caches query-plans, so any queries with the same args can potentially re-use the plan.

请注意,sp_ExecuteSQL缓存查询计划,因此具有相同args的任何查询都可能会重新使用该计划。

The downside is that unless you sign the SPROC, the caller needs SELECT permissions on the table (not just EXEC permissions on the SPROC).

缺点是除非您签署SPROC,否则调用者需要对表的SELECT权限(而不仅仅是SPROC上的EXEC权限)。

#2

I would handle it this way.

我会这样处理它。

WHERE Thing = ISNULL(@Thing, Thing)

If you're just using the parameter as a filter on the where clause, this will work very well. It will ignore the parameter if it is null.

如果您只是将参数用作where子句的过滤器,那么这将非常有效。如果参数为null,它将忽略该参数。

#3

I generally use

我一般用

WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)

etc.

#4

I'm not sure if it is the 'optimal' way, but this is exactly what I do in my stored procedures for the same purposes. My gut feeling is that this is faster than a dynamically created query purely from an execution plan standpoint. The other option is to create a query for each combination of these "flags" that you are passing in, but that really isn't that scalable.

我不确定它是否是“最佳”方式,但这正是我在存储过程中为同样目的所做的事情。我的直觉是,这比纯粹从执行计划的角度来看动态创建的查询要快。另一种选择是为您传入的这些“标志”的每个组合创建一个查询,但实际上并不是那么可扩展。

#5

A technique I’ve used in the past for this scenario is to utilize the COALESCE function as part of my WHERE clause. Books Online will provide more in depth info on the function, but here’s a snippet of how you can use it in the scenario you described:

我过去在这种情况下使用的一种技术是将COALESCE函数用作WHERE子句的一部分。联机丛书将提供有关该功能的更多深入信息,但这里有一个如何在您描述的场景中使用它的片段:

create procedure usp_TEST_COALESCE
(
    @parm1 varchar(32) = null,
    @parm2 varchar(32) = null,
    @parm3 int = null
)
AS

SELECT * 
FROM [TableName]
WHERE Field1 = COALESCE(@parm1, Field1)
AND Field2 = COALESCE(@parm2, Field2)
AND Field3 = COALESCE(@parm3, Field3)

The COALESCE function will return the first non-null expression from its arguments. In the example above, if any of the parameters are null, the COALESCE function will use the value in the underlying field.

COALESCE函数将从其参数返回第一个非null表达式。在上面的示例中,如果任何参数为null,则COALESCE函数将使用基础字段中的值。

One important caveat to using this technique is that the underlying fields in the table (that make up your where clause) need to be non-nullable.

使用这种技术的一个重要警告是,表中的底层字段(构成where子句)必须是不可为空的。

#6

Look at the following link in the section titled "The Case Study: Searching Orders". This explores all options in depth and should give you an excellent overview of the costs associated with each of these options. Warning, be very careful when using COALESCE it may not return what you think it is.

请查看标题为“案例研究:搜索订单”部分中的以下链接。这将深入探讨所有选项,并且应该为您提供与每个选项相关的成本的完美概述。警告,使用COALESCE时要非常小心它可能不会返回你的想法。

Regards,

Tim

#7

This is the method I typically use. I see no reason for it to be inefficient, as the statement should short-circuit to true if @thing is null, and would therefore not require a table scan. Do you have any evidence that this comparison is slowing your query? If not, I would not worry about it.

这是我通常使用的方法。我认为没有理由使它效率低下,因为如果@thing为null,语句应该短路为真,因此不需要表扫描。您是否有任何证据表明这种比较会减慢您的查询速度?如果没有,我不担心。

#8

when you declare the parameters if you set a value to them such as null in your case you do not need to pass a value in to them unless of course you need to. I use this ability to flag if another query needs to be run is special cases when the parameter is not null

当你为它们设置一个值时声明参数,例如在你的情况下为null,你不需要将值传递给它们,除非你需要。我使用此功能来标记是否需要运行另一个查询是特殊情况,当参数不为null时

I typically just check it like this

我通常只是这样检查一下

IF field IS NULL

IF字段为空

#9

Thanks, This was helpful. I have decided to use the sp_ExecuteSQL method due to the potential performance advantages mentioned. I have a slightly different take on it which you may find helpful.

谢谢,这很有帮助。由于提到的潜在性能优势,我决定使用sp_ExecuteSQL方法。我对它有一些不同的看法你可能会觉得有帮助。

DECLARE @sql nvarchar(4000) 
DECLARE @where nvarchar(1000) =''

SET @sql = 'SELECT * FROM MyTable'

IF @Param1 IS NOT NULL 
    SET @where = @where + ' AND Field1 = @Param1'

IF @Param2 IS NOT NULL 
    SET @where = @where + ' AND Field2 = @Param2' 

IF @Param3 IS NOT NULL 
    SET @where = @where + ' AND Field3 = @Param3' 

-- Add WHERE if where clause exists, 1=1 is included because @where begins with AND
IF @where <> ''
    SET @sql = @sql + ' WHERE 1=1' + @where

--Note that we could also create order parameters and append here
SET @sql = @sql + ' ORDER BY Field1'

#1