Hi,

Why does this code execute correctly and returns 3 rows in Query Analyzer

SELECT ImportID FROM TempTable
    WHERE Field1 NOT IN ('', 'K', 'KE', 'KR')

but returns all rows in the table when executed as stored procedure

CREATE PROCEDURE dbo.[StoredProcedure] AS

SELECT ImportID FROM TempTable
    WHERE Field1 NOT IN ('', 'K', 'KE', 'KR')
GO


Morten

Removing '' and checking for null seems to fix it, but that doesn't explain the difference between query analyzer and stored procedure

CREATE PROCEDURE dbo.[StoredProcedure] AS

SELECT ImportID FROM TempTable
    WHERE Field1 NOT IN ('K', 'KE', 'KR') AND Field1 IS NOT NULL
GO

Definitely sounds like you have SET ANSI_NULLS OFF for the proc. Set it
ON. Don't use Enterprise Manager to create procs. Use Query Analyzer
and then you shouldn't have this problem.

Definitely guilty of creating them in Enterprise Manager :o, setting ANSI_NULLS_ON while editing in query analyzer fixed it :)

Thanks

Morten




On Fri, 04 Nov 2005 11:03:05 +0100, David Portas <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote:

Show quoteHide quote

One possible reason could be the ANSI_NULLS setting that is persisted
with the stored proc. Script the proc from the Object Browser in Query
Analyzer to verify that SET ANSI_NULLS is ON for the proc.

If that doesn't help then please post enough code so that we can
reproduce the problem. Your code fragment doesn't help. At least DDL,
INSERT(s) of some data and the SET options for the proc are needed.

How to calculate comparisons of fields between records
Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC