Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Have existing Sp's working before with <> NULL

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-02-21 : 11:48:16
I have a lot of sp's working with <> NULL check, and now same version of sql server on a diff server but on that <> NULL isn't working.

Now had to change it to is not NULL.

Can you please kindly tell me which is the better way to check validate <> NULL or is not NULL.

Thank you very much for the helpful info.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-21 : 15:00:01
There is only one way. You must use "IS NOT NULL"

In SQL, nothing is equal to NULL, not even NULL. Also, nothing is NOT equal to NULL.

e.g. all these return false:

NULL = NULL
NULL <> NULL
anything = NULL
anything <> NULL
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-02-21 : 19:33:00
You've fallen victim to a different SET ANSI_NULLS setting. As already stated you can't rely on (in)equality comparisons to null, use the IS NULL comparison only.
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-02-24 : 04:44:58
set ansi_nulls off will allow you to compare NULL = NULL
NULL <> NULL
anything = NULL
anything <> NULL

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -