Comparing NULL valued - Max2006

Hi,

Is there any server setting (or database setting) that allows me to compare
null values? Something like this:

SELECT * FROM EMPLYEES WHERE NAME = NULL

Thank you,
Max

Comparing NULL valued - SQL Menace

You need IS not =
SELECT * FROM EMPLYEES WHERE NAME IS NULL

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

Comparing NULL valued - mohitkgupt

SELECT * FROM EMPLYEES WHERE NAME IS NULL

BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/cdc45cd8-e9b6-4648-8417-892fbeab15af.htm

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Comparing NULL valued - Tibor Karaszi

In addition to the other posts, you can do SET ANSI_NULLS OFF. I *do not* recommend you to use this,
I just post it here with a note to avoid it, in case you read about it elsewhere. Also, in the
current version of the Katmai Books Online, this is listed as deprecated and you don't want to write
code today that you know will not work in a future version of SQL Server.

So, use IS NULL, as already suggested.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Comparing NULL valued - Max2006

Thanks Tibor. This is exactly what I was looking for. Thanks for help and
the tip.

Comparing NULL valued - Max2006

Hi Tibor,

Do you know online resource that explains why SET ANSI_NULLS OFF is not
going to be supported in Katmai and why it is not recommanded?

Thank you,
Ali

Comparing NULL valued - Tibor Karaszi

It isn't recommended because

a) It basically doesn't rhyme with the mathematical model that SQL based systems are based on. (For
the relational purists out there: Yes, I'm aware of SQL drawbacks and I don't want to get into a
theoretical debate here... )

b) It doesn't behave according to the ANSI SQL standard.



Download and install Katmai (in a virtual machine), check out Books Online and the deprecation list.
This is where you find it is (currently planned) as deprecated. As to why, see above.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Comparing NULL valued - Erland Sommarskog

Max2006 (alanalan1@newsgroup.nospam) writes:

SET ANSI_NULLS OFF is supported in Katmai. But it is deprecated, which
it in fact is in SQL 2005 as well, see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c10eeaa5-3d3c-49b4-a4bd-
5dc4fb190142.htm

That something is deprecated means that usage of it is strongly discouraged.

ANSI_NULLS OFF is a legacy, together with a couple of more options, and
it will only be an advantage if they go away. If you want to right code
that is robust, you will need to consider both possibilities, which
means increased level of complexity, not the least in testing.

Recently, a colleague of mine had problems running a stored procedure
that I had written. I was mystified, because I had run the procedure
before, and the error he got was such that it should appear on every
execution: a syntax error in a piece of dynamic SQL. But as I searched
a little more, I found the reason: there was a mistake in the code,
but with CONCAT_NULL_YIELDS_NULL on, that did not matter, since the
resulting string became NULL. (The string should not have been built at
all.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx