Saul Margolis (saul_margolis@hotmail.com) writes:
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
>
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
---------------
> set ANSI_NULLS off
>
> drop table #TestNull
>
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
>
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
>
> declare @TestVar varchar(10)
>
> select @TestVar = Null
>
> select * from #TestNull where Field1 = @TestVar
> ---------------

I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.

Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:

o Indexed views.
o Index on computed columns.
o Access to linked servers.

With ANSI_NULLS off, these features are unavailable.

Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.

Also, you may need to have a go for ANSI_NULLS to have effect.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp