ANSI_NULLS and null comparison

[Saul Margolis]

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

[CJ Butcher]

Re: ANSI_NULLS and null comparison

Your SQL Server select should look like this:

SELECT * FROM #TextNull WHERE Field1 IS NULL

CJ
"Saul Margolis" <saul_margolis@ hotmail.com> wrote in message
news:c20aa081.0 402171416.74430 3af@posting.goo gle.com...[color=blue]
> 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[/color]

[Simon Hayes]

Re: ANSI_NULLS and null comparison


"Saul Margolis" <saul_margolis@ hotmail.com> wrote in message
news:c20aa081.0 402171416.74430 3af@posting.goo gle.com...[color=blue]
> 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[/color]

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

[Erland Sommarskog]

Re: ANSI_NULLS and null comparison

Saul Margolis (saul_margolis@ hotmail.com) writes:[color=blue]
> 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?
>[/color]
---------------[color=blue]
> 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
> ---------------[/color]

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/techinfo/productdoc/2000/books.asp

[Joe Celko]

Re: ANSI_NULLS and null comparison

The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?

Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)

That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!