Inconsistent SQL results

[chloe.crowder]

Hi

I have an oddity. If I run a piece of SQL:

SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Empl oyee
where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
AND MailTo NOT IN ( '3', 'x')
order by MailTo

I get the results

EmployeeNo MailTo
----------- ------
608384 1
606135 1
608689 1
609095 1
607163 1
606165 1
606472 1
608758 1
.....
for 2594 rows

If I create a stored procedure with the same SQL:-

CREATE PROCEDURE dbo.PPS_test
AS
SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Empl oyee
where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
AND MailTo NOT IN ( '3', 'x')
order by MailTo
GO

and run it:-

EXEC PPS_test

I get three extra rows

EmployeeNo MailTo
----------- ------
607922 NULL
606481 NULL
605599 NULL
606316 1
608871 1
607427 1
608795 1
.....
for 2597

Does anyone know what is happening here? It appears that the clause:-

MailTo NOT IN ( '3', 'x')

excludes NULL in raw SQL, but includes NULL (correctly I think) in a
stored procedure.

Chloe Crowder
The British Library

[Alexander Kuznetsov]

verify your ansi_nulls settings:

create table #t(i int)
insert into #t
select null
union all
select 1
union all
select 2
union all
select 3
go
select i from #t where i not in(1)

i
-----------
2
3

(2 row(s) affected)
go
set ansi_nulls off
go
select i from #t where i not in(1)

i
-----------
NULL
2
3

(3 row(s) affected)

go
drop table #t

[eric.nave]

ch***********@b l.uk wrote:

Hi

I have an oddity.

Well, I'm not able to duplicate your problem with the sql below. Can
you possibly provide create table and insert statements that show the
strange behavior?

create table Employee (
EmployeeNo int,
MailTo char(1) NULL
)
insert Employee values (608384,'1')
insert Employee values (606135,'1')
insert Employee values (608689,'x')
insert Employee values (609095,'3')
insert Employee values (607922,'2')
insert Employee (EmployeeNo) values (606481)
insert Employee (EmployeeNo) values (605599)

select EmployeeNo, MailTo
from Employee
where MailTo NOT IN ( '3', 'x')
order by MailTo
go

create procedure PPS_test as
select EmployeeNo, MailTo
from Employee
where MailTo NOT IN ( '3', 'x')
order by MailTo
go

exec PPS_test

drop table Employee
drop procedure PPS_test

[Erland Sommarskog]

(ch***********@ bl.uk) writes:

If I create a stored procedure with the same SQL:-

CREATE PROCEDURE dbo.PPS_test
AS
SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Empl oyee
where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
AND MailTo NOT IN ( '3', 'x')
order by MailTo
GO

and run it:-

EXEC PPS_test

I get three extra rows

EmployeeNo MailTo
----------- ------
607922 NULL
606481 NULL
605599 NULL

Let me guess: you are creating your stored procedures in Enterprise
Manager, aren't you? That's a crappy tool to edit stored procedures
in. You are better off using Query Analyzer.

One reason it's crappy is because, it defaults the settings
ANSI_NULLS and QUOTED_IDENTIFI ER to be OFF. These settings are
saved with the procedure, so when you run the procedure ANSI_NULLS
is off, and you get three extra rows. Normally, when ANSI_NULLS is
ON (which is the default in most environments), NULL is never
equal to anything, and never is it unequal to anything. But when the
setting is OFF NULLs are equal to other NULLS and unequal to other
values. This is a legacy setting that should be avoided.

There are also features in SQL Server that requires ANSI_NULLS to
be ON, so there is all reason to run with ANSI_NULLS on.

In Query Analyzer, ANSI_NULLS is ON by default.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[chloe.crowder]

Erland

Ah, that would certainly explain it. Don't you just love Microsoft at
times!

Does the 'feature' carry forward into SQL 2005?

Thanks to Eric and Alexander for their time

Chloe

[Erland Sommarskog]

(ch***********@ bl.uk) writes:

Ah, that would certainly explain it. Don't you just love Microsoft at
times!

Does the 'feature' carry forward into SQL 2005?

The legacy settings SET ANSI_NULLS OFF and SET QUOTED_IDENTIFI ER OFF
remains. But in the new Mgmt Studio it is not equally easy to run with
these settings off. (There are alas some cases where they still turn them
off, despite that I have hammered them with bug reports about this.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx