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

问题描述

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

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

1. IS NULL
2. 不为空
3. ANY VALUE (NULL AND NOT NULL)(基本上没有 WHERE 子句)

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

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

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?

推荐答案

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

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

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

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

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

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

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

这篇关于WHERE IS NULL、IS NOT NULL 或 NO WHERE 子句取决于 SQL Server 参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！