Problem with NULL in SQL 2000

[vbobba]

I have below query inside stored procedure
select groupno,a from table1
where groupno <> 'Test' is

It is not returning rows where groupno is null. I did recompile sp with SET ANSI_NULLS off. But it is not working. Any suggestions? Please help

[chazzy]

This is normal behavior.

Just add the null check to the select..


select groupno,a from table1
where groupno is null or groupno <> 'Test'


It's awkward looking, but works... I've not seen any change to null settings that fixes it.

[mattyblah]

set ansi_null could throw it off. you could also use the function isnull to condense the statement.

[vbobba]

Inside stored procedure I have so many of these kind of querys on different fields. That why I tried to set ANSI_NULLS OFF. but it didnt work.Is there anything I can set at the stored procedure level instead at field level? Thank you

[Technoroj]

use if null set to default!