"Saul Margolis" <saul_margolis@hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@posting.google.c om...
> Hi All,
>
> 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
> ---------------
>
>
> Thanks in advance,
> Saul

I guess you mean WHERE Field2 = @TestVar ? That will return the (1, NULL)
row.

See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.

Simon