需要根据变量是否为空从表中选择记录.如果为 null，则检查不等于条件

Question

对于每天在SQL上工作的人来说，这听起来很容易，这占用了我很多时间。 我有表格，如果值设置为空，我需要根据变量值从中选择记录状态列不等于3，是变量值不等于空的过滤器基于变量值。

DECLARE @status int
 set @status  = 1
 SELECT change_set_history_id, files_changed, is_previewed 
    FROM dbo.ChangeSetHistory WITH (NOLOCK)
    WHERE [user_name] = 'djacob' AND 
          [culture] = 'nl-NL' AND
          (@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status) AND
          [approver] = null    AND    
          is_deleted = 0

如果它尝试使用此部分查询，它可以工作声明@status int设置@status = 1从 ChangeSetHistory 中选择 *，其中状态 = @status，user_name = 'djacob' 和区域性 = 'nl-NL'

Answer 1

只需用另一对括号包裹您的OR条件：

SELECT change_set_history_id, files_changed, is_previewed 
FROM dbo.ChangeSetHistory WITH (NOLOCK)
WHERE [user_name] = 'djacob' AND 
      [culture] = 'nl-NL' AND
      ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status)) AND
      [approver] IS null    AND    
      is_deleted = 0

如果您的状况相当于：

    WHERE ([user_name] = 'djacob' AND [culture] = 'nl-NL' AND (@status IS NULL AND [status] <> 3)) OR 
      ((@status IS NOT NULL AND [status] =     @status) AND [approver] = null    AND  is_deleted = 0)

测试用例：

DECLARE @status int
SET @status = 1
    SELECT *
    FROM (
        SELECT NULL [status] UNION ALL
        SELECT 1 [status] UNION ALL
        SELECT 2 [status] UNION ALL
        SELECT 3 [status]
    ) T
    WHERE ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status));

我看到的另一个问题是[approver] = null条件。[approver] is null

Answer 2

尽量不要在同一上下文中混合AND和OR。

您错过了状态条件的全局括号：

而不是

(@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status)

它必须是

((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status))

它应该可以解决您的问题：

DECLARE @status int
 set @status  = 1
 SELECT change_set_history_id, files_changed, is_previewed 
    FROM dbo.ChangeSetHistory WITH (NOLOCK)
    WHERE [user_name] = 'djacob' AND 
          [culture] = 'nl-NL' AND
          ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status)) AND
          [approver] = null    AND    
          is_deleted = 0