Including NULL Option in WHERE Clause

Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statemen
t
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:

> Hi,
> I'm trying to create predicates that will work the same way whether a fiel
d
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statem
ent
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THE
N null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CAS
E
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the seco
nd
> returns TRUE.
> I would further like to be able to construct this solution or any other th
at
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
[vbcol=seagreen]
> I think you're looking for the ISNULL function. It would be used like thi
s:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
>|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...[vbcol=seagreen]
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts th
e
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the origin
al
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
>
>