哪里是NULL，是不是NULL或WHERE子句取决于SQL Server参数值

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 WHERE clause depending on its value - the problem is that the 3 values would be where MyColumn

我在SQL Server 2000中有一个基于参数值执行搜索的存储过程。对于传入的一个参数，我需要一个不同的WHERE子句，取决于它的值——问题是，3个值将是MyColumn所在的位置。

IS NULL
为空
IS NOT NULL
不是零
ANY VALUE (NULL AND NOT NULL) (essentially no WHERE clause)
任何值(NULL和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 ... END branching?

我在构思正确的语法时遇到了一些障碍。是否可以在一个select语句中执行，而不执行IF @parameter BEGIN…分支?

6 个解决方案

#1

Here is how you can solve this using a single WHERE clause:

以下是使用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:

对案例陈述的幼稚用法不起作用，我的意思是:

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的答案

#2

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.

就像这样。

#3

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;

#4

WHERE MyColumn = COALESCE(@value,MyColumn)

If @value is NULL, it will compare MyColumn to itself, ignoring @value = no where clause.

如果@value为NULL，它会将MyColumn与自身进行比较，忽略@value = no where子句。
IF @value has a value (NOT NULL) it will compare MyColumn to @value.

如果@value有一个值(非NULL)，它会将MyColumn与@value进行比较。

Reference: COALESCE (Transact-SQL).

参考:合并(transact - sql)。

#5

An other way of 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

#6

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 = '')

#1