NULL values handled differently in stored procedure

Can someone explain why the following is happening:
SELECT EventId, MDResponse FROM tblEvent
WHERE (MDResponse NOT IN(1, 3))
ORDER BY MDResponse
If I run the above statement in Query Analyzer it returns
all rows where MDResponse is not 1 or 3. No NULL
MDResponse rows are returned. This is what I expected.
If I run it as a stored procedure it also returns the
NULL valued rows.
Why the difference?
Thanks
MikeLooks like your SP was created with the setting SET ANSI_NULLS OFF. The
setting is persisted with the SP.
Re-create the proc with SET ANSI_NULLS ON:
SET ANSI_NULLS ON
GO
CREATE PROC ...
--
David Portas
SQL Server MVP
--|||It's possible this is due to the infamous ANSI-NULL handling, which the
analyzer sets to a default value that is different than the server itself
(IIRC). For instance the analyzer will handle xxx != null, whereas that will
fail in a storedproc. That said, I can't imagine why you'd get this
particular case if that was the problem. See about turning ANSI NULL off in
the analyzer, and see if you suddenly get the nulls in there too.
I'd also like to point out that the performance guide says anything with a
NOT is slower.