在 SQL 中使用带有参数/变量的 Case 语句来检查 Null 值

Using Case Statement in SQL with parameter/variable to check for Null values

提问by jpuck1054700

I am trying to write a SQL Select statement to return records based on a user input through a front end. I want to write the Select statement like this:

我正在尝试编写一个 SQL Select 语句，以根据通过前端的用户输入返回记录。我想像这样编写 Select 语句：

SELECT somefields
FROM sometable
WHERE CASE variable
         WHEN 'blank' THEN field IS NULL
         ELSE field = field
      END

Basically I either want to filter a column to find NULL values or ignore the filter and return all values depending on the value of the variable. I know that the results of the CASE statement is not executable but how can I do this?

基本上，我要么想过滤一列以查找 NULL 值，要么忽略过滤器并根据变量的值返回所有值。我知道 CASE 语句的结果不可执行，但我该怎么做？

回答by Branko Dimitrijevic

When variableis 'blank', the following query will give you rows where fieldis NULL. When variableis anything else, it will give you all rows:

当variable是 'blank' 时，以下查询将为您提供fieldNULL 的行。如果variable是别的什么，它会给你的所有行：

SELECT somefields
FROM sometable
WHERE
    (variable = 'blank' AND field IS NULL)
    OR (variable <> 'blank')

回答by Andomar

The following snippet should behave as follows:

以下代码段应如下所示：

• when @variable is null, return all rows
• when @variable = 'blank', return all rows where field is nullor field = 'blank'
• otherwise, return rows where @variableequals field

• when @variable is null，返回所有行
• when @variable = 'blank'，返回所有行 wherefield is null或field = 'blank'
• 否则，返回@variable等于的行field

Code snippet:

代码片段：

WHERE 1 = CASE
      WHEN @variable is null then 1
      WHEN @variable = 'blank' and field is null then 1
      WHEN @variable = field then 1
      END

回答by bhamby

You can use NULLIF()(link is for SQL Server, but NULLIF()should be standard):

您可以使用NULLIF()（链接适用于 SQL Server，但NULLIF()应该是标准的）：

SELECT somefields
  FROM sometable
 WHERE field = NULLIF(variable, 'blank')

回答by Ali

SELECT somefields
FROM sometable
WHERE ((variable IS NULL OR variable = 0) OR (variable = field))

WHERE Criteria is apply when variable have value
For Example:

当变量具有值时适用 WHERE 标准
例如：

DECLARE @CityName VARCHAR(50)
SET @CityName = NULL

SELECT CityName
FROM City
WHERE ((@CityName IS NULL ) OR (@CityName = CityName ))

When City is null then tables return all rows

当 City 为 null 时，表返回所有行

回答by James Prout

Think I understand what you mean....for example....

想我明白你的意思......例如......

    SELECT 
          House, Postcode 
    from 
          SomeTable 
    where
          (House=isnull(@House,House) or (House is null and @House is null))
    and
          (Postcode=isnull(@Postcode,Postcode) or (Postcode is null and @Postcode is null))

First bit of the conditional where is to use the variable, when present (the isnull bit is to ignore the variable if it's null)

条件 where 的第一位是使用变量，当存在时（isnull 位是忽略变量，如果它为空）

Second bit of the conditional where is in case your evaluative field is null also as effectively fields don't = null they are 'is null'.

条件 where 的第二位是在您的评估字段为空的情况下，同样有效的字段 don't = null 它们是“为空”。

Confused? Good. Works on what I'm doing though!

使困惑？好的。虽然适用于我正在做的事情！

回答by Tahir Khalid

Here is my solution based on @Andomar answer above aimed at anyone testing an input varchar value, you need to test the parameter in the right order as per the example below:

这是我基于上面@Andomar 答案的解决方案，针对测试输入 varchar 值的任何人，您需要按照以下示例以正确的顺序测试参数：

FIELD1 = CASE
  WHEN @inputParameter = '' THEN FIELD1
  WHEN @inputParameter <> FIELD1 THEN NULL  -- if input is some text but does not match
  WHEN @inputParameter IS NULL THEN FIELD1
  WHEN @inputParameter != '' AND FIELD1 = @inputParameter THEN FIELD1
END

Hope this helps someone.

希望这可以帮助某人。

回答by Zann Anderson

I think I get what you're after. Something like this maybe?

我想我明白你的追求。也许像这样的东西？

SELECT field1,
    field2,
    CASE variable
        WHEN 'blank' THEN NULL
        ELSE field3
    END as field3
FROM sometable