By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

Inconsistent SQL results

P: n/a
Hi

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

SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Employee
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.Employee
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

Apr 11 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Apr 11 '06 #2

P: n/a
ch***********@bl.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

Apr 11 '06 #3

P: n/a
(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.Employee
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_IDENTIFIER 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****@sommarskog.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
Apr 11 '06 #4

P: n/a
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

Apr 12 '06 #5

P: n/a
(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_IDENTIFIER 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****@sommarskog.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
Apr 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.