在 WHERE 子句中应用条件过滤

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

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 

无需过滤即可正常工作。

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

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 语句来实现这一点。

我尝试了以下方法：

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.

首先，这个((J.Id = @Jobid and @Jobid>0) or @Jobid=0)可以更换
有了这个(@Jobid = 0 or J.Id = @Jobid)。 请注意，自从0显然不是工作 ID（或员工或领导）的有效值，and部分是不相关的，因为没有记录将包含 id 0。

二、不要使用0作为无效值，使用null反而。它不会影响性能，但这是一个更好的编程习惯，因为0在其他情况下很可能是有效值。

第三，众所周知，包罗万象的查询会受到性能影响，尤其是在存储过程中，因为缓存的执行计划可能不是当前执行的最佳执行计划。据我所知，处理此问题的最佳方法是向查询添加重新编译提示，如中所建议的本文 and in 那篇文章.

因此，我建议您的查询如下所示：

CREATE PROCEDURE <procedure name>
(
        @Jobid      INT=NULL,
        @leadid     INT=NULL,
        @employeeid INT=NULL
)
AS

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 
WHERE (@Jobid IS NULL OR J.Id = @Jobid)
AND (@leadid IS NULL OR l.Id = @leadid)
AND (@employeeid IS NULL OR e.Id = @employeeid)
OPTION(RECOMPILE)

GO

通过正确的表索引通常可以提高选择性能。然而，正确建立索引需要的知识并非所有开发人员都具备。这是一个非常值得一读的主题。我会从这里开始.