Posted by Erland Sommarskog on 04/12/06 00:41

(chloe.crowder@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]