Posted by chloe.crowder on 04/11/06 19:00

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

[Back to original message]