I have a stored procedure that has a parameter that can be either NULL or non-NULL value. I need to compare this parameter in the WHERE clause. If the parameter is non-NULL,
我有一个存储过程，它的参数可以是空值或非空值。我需要在WHERE子句中比较这个参数。如果参数是非空的，
where ...
and parameter = non-NULL-value
will work.
将工作。
But when the parameter will be null, it will not be ANSI-compliant:
但是当参数为null时，它将不符合ansi:
where ...
and parameter = NULL
I don't want to write two separate queries. How do I ensure ANSI-compliance in the same query?
我不想写两个独立的查询。如何确保在同一查询中遵从ansi ?
1 个解决方案
#1
4
Assuming that the value is passed to your query in a @value parameter, you can do it like this:
假设值以@value参数传递给查询，您可以这样做:
SELECT *
FROM MyTable
WHERE (@value IS NOT NULL AND parameter = @value) -- Or simply parameter = @value
OR (@value IS NULL AND parameter IS NULL)
#1
4
Assuming that the value is passed to your query in a @value parameter, you can do it like this:
假设值以@value参数传递给查询，您可以这样做:
SELECT *
FROM MyTable
WHERE (@value IS NOT NULL AND parameter = @value) -- Or simply parameter = @value
OR (@value IS NULL AND parameter IS NULL)