在T-SQL中忽略NULL参数 [英] Ignoring a NULL parameter in 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.

我正在这样做:

(@thing IS NULL or Thing=@thing) 

这是对的吗?如果是这样，它的性能会不好吗?与单独构造SQL相比，它似乎要慢得多.

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

执行此操作的最佳方法是什么?

What's the optimal way to do this?

已修复！请参阅马克·格雷夫(Marc Gravell)的答案.总而言之，多次使用IS NULL会对性能产生很大的影响.

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

推荐答案

一旦您获得了不止这些，就可以了:它开始变得非常缓慢.在这种情况下，我倾向于使用生成的TSQL-即

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.

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

等

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

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

不利之处在于，除非您对SPROC签名，否则调用者需要表的SELECT权限(而不仅仅是SPROC的EXEC权限).

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

这篇关于在T-SQL中忽略NULL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！