CODEWITHSUNDEEP
t-sqlsql-server-2008stored-procedures
Jasper
Jasper

Reputation: 1837

Comparing nullable fields in stored procedure

I'm having a problem with a stored procedure. It's comparing a new record with an existing one to check for changes. This is done like this:

SELECT 1 FROM Table
WHERE Id= @Id
AND Field1 = @Field1
AND Field2 = @Field2
AND Field3 = @Field3
AND Field4 = @Field4
AND Field5 = @Field5
AND LEDTS IS NULL

This seems to fail when fields are NULL. I know = NULL doesn't work the way you might expect it to, but I didn't know it also fails in things like this. When I change the field comparison to things like

(Field1 IS NULL AND Field2 IS NULL) OR Field1 = @Field2

it does work. Is there a better way to do this?

Upvotes: 1

Views: 98

Answers (3)

Callie J
Callie J

Reputation: 31296

This behaviour is governed by the ANSI_NULLS setting. This defaults to ON, and needs to be set this way for various things to work correctly (such as indexed views and distributed queries), but if you don't require those, you could switch it off.

Read more about it at Books Online.

Upvotes: 1

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You can use checks like this:

ISNULL(Field1, '') = ISNULL(@Field1, '')

There is a drowback doing this: you can have equalities like 

NULL='' or ''=NULL

I dont't know if it could be acceptable in your scenario.

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

SET ANSI_NULLS can control this behavior... Setting SET ANSI_NULLS OFF will make NULL = NULL evaluate to true.

But I would recommend against actually setting SET ANSI_NULLS OFF, and instead try to limit the fields in the database that are "nullable".

Upvotes: 2

Related Questions