WHERE clause and NULL values - agendum9

I have this simple query:

DELETE FROM MyTable
WHERE Field = @field

But this doesn't work if @field is NULL.  To work around this I
thought of this but it has a problem:

DELETE FROM MyTable
WHERE ISNULL(Field, 0) = ISNULL(@field, 0)

The problem is, this will delete a row for values of zero when I am
trying to delete NULL values.  So the only other logical method I've
come up with is:

DELETE FROM MyTable
WHERE (@field IS NULL AND Field IS NULL) OR
(@field IS NOT NULL AND Field = @field)

But it doesn't seem very efficient.  Is there a better way to do this?

Thanks

WHERE clause and NULL values - Adi

When you use the isnull function on the column, you prevent the use of
an index on that column, so try to avoid it even if logically it does
give you the solution.

In cases like you showed I just use an if statement.  In this case
there will be 2 delete statements but each one will have is own query
plan.  My code would look like this:

IF @Field is null
DELETE FROM MyTable WHERE Field is null
ELSE
DELETE FROM MyTable WHERE field = @field

Adi

WHERE clause and NULL values - --CELKO--

1) Start using COALESCE(), which is standard and does not make the
same mistakes that ISNULL() does


1) Use an impossible value instead of zero:

CREATE TABLE Foobar
( ..
something_column INTEGER
CHECK (something_column >= 0),
..);

DELETE FROM MyTable
WHERE COALESCE (something_column, -666) = COALESCE(@my_parm,
-666) ;

2)  A little weird, but it will work:

DELETE FROM MyTable
WHERE something_column = @my_parm
OR COALESCE (something_column, @my_parm) IS NULL;

WHERE clause and NULL values - AlexS

You have one more option: ANSI_NULLS setting.
http://msdn.microsoft.com/en-us/library/ms188048.aspx

In current version of MSDN it is marked as "not recommended to change"
but it still works.
Basically turning this setting off makes you query behave the way you
want it to: Field =3D NULL will filter those records with Field set to
NULL.

So it should look lite this:

SET ANSI_NULLS OFF

=2E. -- do what you want

SET ANSI_NULLS ON -- this is the default setting so we'd better
preserve it

HTH

WHERE clause and NULL values - Tony Rogerson

WHERE Field = @field
OR ( @field is null and Field is null )

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

WHERE clause and NULL values - Tony Rogerson

Bad advice just can't get any better than the above.

Does make me remember how I use to have to code in PL/1 though - thanks for
the memory.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]