选择SQL查询，其中parameter1或parameter2

Question

CREATE PROCEDURE [dbo].[SPPeople]
(
   @Name varchar(50) = null,
   @Status1 char(1) = '0',
   @Status2 char(1) = '0',
   @Status3 char(1) = '0'
)
as
   SELECT 
      People.Name, People.Age 
   FROM 
      People
   WHERE
      (People.Name = CASE WHEN @Name is null THEN People.Name ELSE @Name END) 
      AND
      ((People.Status1 = CASE WHEN @Status1 = '0' THEN People.Status1 ELSE @Status1 END) 
       OR
       (People.Status2 = CASE WHEN @Status2 = '0' THEN People.Status2 ELSE @Status2 END)
       OR
       (People.Status3 = CASE WHEN @Status3 = '0' THEN People.Status3 ELSE @Status3 END))

如果参数@Status1和@Status2等于1，则查询应该返回寄存器，这些寄存器的Status1或Status2必然等于1，而不依赖于Status3。

但是，当我将@Status1和@Status2作为1传递时，上面的查询将返回所有寄存器。

Answer 1

试着使用这个逻辑来代替：

SELECT p.Name, p.Age 
FROM People p
WHERE (@Name is null OR p.Name = @Name) AND
      (@status1 <> '0' and p.Status1 = @status1 or
       @status2 <> '0' and p.Status2 = @status2 or
       @status3 <> '0' and p.Status3 = @status3
      );

大多数人发现在case子句中理解where表达式比布尔逻辑更困难。

Answer 2

我认为您可以修改您的WHERE条件，以便直接与参数进行比较，例如

WHERE
People.Name = @Name 
AND 
(
People.Status1 = @Status1 
OR
People.Status2 = @Status2 
OR
People.Status3 = @Status3 
)