WHERE IS NULL、IS NOT NULL 或 NO WHERE 子句取决于 SQL Server 参数值

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

提问by Russ Cam

I have a stored procedure in SQL Server 2000 that performs a search based on parameter values. For one of the parameters passed in, I need a different WHEREclause depending on its value - the problem is that the 3 values would be where MyColumn

我在 SQL Server 2000 中有一个存储过程，它根据参数值执行搜索。对于传入的参数之一，我需要一个不同的WHERE子句，具体取决于它的值 - 问题是这 3 个值将在哪里MyColumn

1. IS NULL
2. IS NOT NULL
3. ANY VALUE (NULL AND NOT NULL)(essentially no WHEREclause)

1. IS NULL
2. IS NOT NULL
3. ANY VALUE (NULL AND NOT NULL)（基本上没有WHERE条款）

I'm having some mental block in coming up with the correct syntax. Is this possible to do in one select statement without performing some IF @parameter BEGIN ... ENDbranching?

我在想出正确的语法时遇到了一些心理障碍。这是否可以在不执行某些IF @parameter BEGIN ... END分支的情况下在一个 select 语句中完成？

回答by Patrick McDonald

Here is how you can solve this using a single WHEREclause:

以下是使用单个WHERE子句解决此问题的方法：

WHERE (@myParm = value1 AND MyColumn IS NULL)
OR  (@myParm = value2 AND MyColumn IS NOT NULL)
OR  (@myParm = value3)

A na?ve usage of the CASE statement does not work, by this I mean the following:

CASE 语句的幼稚用法不起作用，我的意思是：

SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
    WHEN value1 THEN MyColumn IS NULL
    WHEN value2 THEN MyColumn IS NOT NULL
    WHEN value3 THEN TRUE
END

It is possible to solve this using a case statement, see onedaywhen's answer

可以使用 case 语句来解决这个问题，请参阅 onedaywhen 的答案

回答by BobbyShaftoe

You could just do something like this:

你可以做这样的事情：

SELECT *
FROM foo
WHERE (@param = 0 AND MyColumn IS NULL)
OR (@param = 1 AND MyColumn IS NOT NULL)
OR (@param = 2)

Something like that.

类似的东西。

回答by onedaywhen

This is how it can be done using CASE:

这是如何使用它来完成的CASE：

DECLARE @myParam INT;
SET @myParam = 1;

SELECT * 
  FROM MyTable
 WHERE 'T' = CASE @myParam
             WHEN 1 THEN 
                CASE WHEN MyColumn IS NULL THEN 'T' END
             WHEN 2 THEN
                CASE WHEN MyColumn IS NOT NULL THEN 'T' END
             WHEN 3 THEN 'T' END;

回答by Chad Grant

WHERE MyColumn = COALESCE(@value,MyColumn) 

• If @valueis NULL, it will compare MyColumnto itself, ignoring @value = no whereclause.

• IF @valuehas a value (NOT NULL) it will compare MyColumnto @value.

• 如果@value是NULL，它将MyColumn与自身进行比较，忽略 @value = no where子句。

• IF@value有一个值 ( NOT NULL) 它将MyColumn与 @value.

Reference:COALESCE (Transact-SQL).

参考：COALESCE (Transact-SQL)。

回答by Croton

An other way of CASE:

CASE的另一种方式：

SELECT *  
FROM MyTable
WHERE 1 = CASE WHEN @myParm = value1 AND MyColumn IS NULL     THEN 1 
               WHEN @myParm = value2 AND MyColumn IS NOT NULL THEN 1 
               WHEN @myParm = value3                          THEN 1 
          END

回答by Marc D

I've had success with this solution. It's almost like Patrick's, with a little twist. You can use these expressions separately or in sequence. If the parameter is blank, it will be ignored and all values for the column that your searching will be displayed, including NULLS.

我在这个解决方案上取得了成功。这几乎就像帕特里克的，有一点点扭曲。您可以单独或按顺序使用这些表达式。如果参数为空，它将被忽略，并且将显示您搜索的列的所有值，包括 NULLS。

SELECT * FROM MyTable
WHERE 
    --check to see if @param1 exists, if @param1 is blank, return all
    --records excluding filters below
(Col1 LIKE '%' + @param1 + '%' OR @param1 = '')
AND
    --where you want to search multiple columns using the same parameter
    --enclose the first 'OR' expression in braces and enclose the entire 
    --expression 
((Col2 LIKE '%' + @searchString + '%' OR Col3 LIKE '%' + @searchString + '%') OR @searchString = '')
AND
    --if your search requires a date you could do the following
(Cast(DateCol AS DATE) BETWEEN CAST(@dateParam AS Date) AND CAST(GETDATE() AS DATE) OR @dateParam = '')

回答by Lukasz Szozda

This kind of logic could be implemented using EXISTS:

这种逻辑可以使用EXISTS：

CREATE TABLE tab(a INT, b VARCHAR(10));
INSERT INTO tab(a,b) VALUES(1,'a'),(1, NULL),(NULL, 'a'),(2,'b');

Query:

询问：

DECLARE @a INT;

--SET @a = 1;    -- specific NOT NULL value
--SET @a = NULL; -- NULL value
--SET @a = -1;   -- all values

SELECT *
FROM tab t
WHERE EXISTS(SELECT t.a INTERSECT SELECT @a UNION SELECT @a WHERE @a = '-1');

db<>fiddle demo

db<>小提琴演示

It could be extended to contain multiple params:

它可以扩展为包含多个参数：

SELECT *
FROM tab t
WHERE EXISTS(SELECT t.a INTERSECT SELECT @a UNION SELECT @a WHERE @a = '-1')
  AND EXISTS(SELECT t.b INTERSECT SELECT @b UNION SELECT @a WHERE @b = '-1');