在 WHERE 子句中应用条件过滤 [英] Apply conditional filtering in WHERE clause

问题描述

我在 SELECT 语句中加入了几个表，如下所示，它有三个参数.

I am joining a few tables in the SELECT statement like below and it has three parameters.

DECLARE @Jobid      INT=0,
        @leadid     INT=0,
        @employeeid INT=0

SELECT e.id,
       l.id,
       j.id,
       e.NAME,
       l.NAME,
       j.NAME
FROM   employee e
       INNER JOIN leads l
               ON e.leadid = l.id
       INNER JOIN Jobs j
               ON j.id = e.Jobid 

无需过滤即可正常工作.

This works fine without filtering.

在 WHERE 子句中，我必须添加如下内容.如果三个 ID 中的任何一个大于零，那么我必须考虑 WHERE 子句中的过滤器；如果它等于零，我不会考虑那个特定条件.

In the WHERE clause I have to add something like below. If any of the three ID's is greater than zero then I have to consider the filter in the WHERE clause; if it is equal to zero I won't consider that particular condition.

If @jobid> 0
then introduce this condition in where clause (j.id=@jobid) 

If @leadid> 0
then introduce this condition in where clause (l.id=@leadid)

If @employeeid> 0
then introduce this condition in where clause (e.id=@employeeid)

我知道如何通过动态 SQL 实现这一点，但我需要一个静态 SQL 语句来实现这一点.

I know how to achieve this through dynamic SQL but I need a static SQL statement to achieve this.

我尝试了以下方法:

where 
((J.Id = @Jobid and @Jobid>0 )
or  @Jobid=0)
and (
(L.Id = @leadid and @leadid>0 )
or  @leadid=0
)
and (
(e.Id = @employeeid and @employeeid >0 )
or  @employeeid =0
)

但是性能受到了影响.

请建议我在静态 SQL 中执行此操作的其他更好方法，尤其是使用 Case When.

Kindly suggest me any other better way of doing this in static SQL especially using Case When.

推荐答案

你可以像这样使用 CASE 表达式:

You can use a CASE expressions like this:

案例示例

WHERE
    CASE 
        WHEN @Jobid > 0 THEN @Jobid     -- When @Jobid supplied use it.
        ELSE J.id                       -- When not; return current value.
    END = J.id

当@Jiobid 超过 0 时，它会与 J.id 进行比较.如果不将 J.id 与自身进行比较，这当然总是会导致匹配.

When @Jiobid exceeds 0 it is compared to J.id. When not J.id is compared to itself, which will of course always result in a match.

我个人更喜欢@jarlh 在上面的评论中建议的方法.简单性使代码更易于遵循.

Personally I prefer the approach suggested by @jarlh in the comments above. The simplicity makes the code easier to follow.

@jarlh 示例

WHERE
    (J.Id = @Jobid or @Jobid=0)

如果这些方法不能提高您的性能，请尝试将您的架构和一些示例记录添加到问题中.您也可以考虑发布执行计划.

If these approaches don't improve your performance try adding your schema and some sample records to the question. You might also consider posting the execution plan.

这篇关于在 WHERE 子句中应用条件过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！