在存储过程中构建动态WHERE子句

Question

我正在使用 SQL Server 2008 Express，我有一个存储过程从表格中进行 SELECT 操作并根据参数进行筛选。我的参数类型包括 nvarchar 和 int。

这是我的问题，我的 WHERE 子句如下：

WHERE [companies_SimpleList].[Description] Like @What 
    AND companies_SimpleList.Keywords Like @Keywords
    AND companies_SimpleList.FullAdress Like @Where
    AND companies_SimpleList.ActivityId = @ActivityId
    AND companies_SimpleList.DepartementId = @DepartementId
    AND companies_SimpleList.CityId = @CityId

这些参数是由我ASP.NET MVC 3应用程序的用户设置的过滤值，int参数可能没有被设置，因此它们的值将为0。这就是我的问题，存储过程将搜索具有例如CityId为0的项目，并且为此返回错误结果。因此，如果能够根据int参数的值是否大于0来动态地生成where子句，那将很好。

提前致谢

- SidAhmed

---

可能是[在SQL 2008存储过程中使用可选的WHERE子句/参数？]的重复问题（https://dev59.com/bErSa4cB1Zd3GeqPU0f6）。 - Tony

Answer 1

尝试使用这个替代方法：

WHERE 1 = 1
AND (@what     IS NULL OR [companies_SimpleList].[Description] Like @What )
AND (@keywords IS NULL OR companies_SimpleList.Keywords        Like @Keywords)
AND (@where    IS NULL OR companies_SimpleList.FullAdress      Like @Where)
...

如果存储过程中的参数@what或@where有任何一个传递了NULL值，那么该条件将被忽略。您可以使用0代替null作为测试值，然后它将类似于@what = 0 OR ...

- Mahmoud Gamal

---

这是我教初级开发人员的机制。SQL Server 进行了良好的优化，这是一种更易于阅读的逻辑形式之一。 - Paul Turner

同事们劝我不要使用这种方法，因为“查询将为每一行评估相同的条件（例如：@what为空），这将影响性能”。他们是对的吗？Tragedian，您在哪里可以找到您所写的“良好优化”的证明？ - Desmond

是的，从性能角度来看，这不是最佳解决方案。但是这个答案是针对 OP 问题的解决方案。你可以从应用程序端处理它，而不是这样做。 - Mahmoud Gamal

如果你像我一样好奇，是否 WHERE 1=1 是解决方案的一部分，我认为不是。它只是为了语法上的方便，使所有的 where 语句排列整齐。 - James Westgate

太棒了！谢谢你。 - KateMak

Answer 2

尝试类似以下的内容：

AND companies_SimpleList.CityId = @CityId or @CityID = 0

- Tony Hopkinson

---

在两个被 OR 运算符合并的术语周围加上括号是一个不错的主意。 - HABO

同意，我有点懒。我也不喜欢依赖优先级。 - Tony Hopkinson

为了完全明确，我是指在原帖查询的情况下，AND companies_SimpleList.CityId = @CityId or @CityID = 0与AND ( companies_SimpleList.CityId = @CityId or @CityID = 0 )是非常不同的。注意括号的位置对条件判断的影响。 - HABO

Answer 3

这里是另一个易于阅读的 SQL Server >=2008 的解决方案

CREATE PROCEDURE FindEmployee
    @Id INT = NULL,
    @SecondName NVARCHAR(MAX) = NULL
AS 
    SELECT  Employees.Id AS "Id",
            Employees.FirstName AS "FirstName",
            Employees.SecondName AS "SecondName"
    FROM Employees
    WHERE Employees.Id = COALESCE(@Id, Employees.Id)
    AND Employees.SecondName LIKE COALESCE(@SecondName, Employees.SecondName) + '%'

- Mario Eis

---

应该避免这种情况，而选择被接受的答案。请参见The Coalesce Trap。 - KiwiPiet