忽略 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 会缓存查询计划，因此任何具有相同参数的查询都可能会重复使用该计划.

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屋！