whereclause - colname in (null,"s")
Mitch,
Not positive, but probably has to do with the value of the ANSI_NULLS
setting.
... col1 IN (NULL, "S", "M")
is logically equivalent to ...
... col1 = NULL or col1 = "S" or col1 = "M"
Check out the section "SET ANSI_NULLS (T-SQL)" in the SQL Server 7.0 Books
Online for an explanation of how ANSI_NULLS works.
From the section above: "The SQL Server ODBC driver and Microsoft OLE DB
Provider for SQL Server automatically set ANSI_NULLS to ON when connecting."
I presume you are using an ODBC or OLE DB connection from your Web page.
I believe that the SQL Server 6.5 Enterprise Manager used db-lib (not ODBC /
OLE DB) to connect. I'm reasonably sure that the SQL Server 7.0 / 2000
Enterprise Manager uses ODBC or OLE DB to connect.
Which version of SQL Server are you using? It is always critical to post
which version you are using.
----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
Quote:
> I have a stored procedure which builds a dynamic
> sql statement and then executes it. The sql
> being built is:
> select col1, col2 from mytable
> where col1 in (null, "S", "M")
> I can call this stored proc from inside of
> enterprise manager and the statement is executed
> correctly (at least executes as I would like).
> When the same stored proc is called from my
> webpage, the null values are omitted.
> I know I could re-write the proc to be:
> where col1 in ("S", "M") or col1 is null
> but can anyone tell me why it executes
> differently depending on where it is called from?
> Thanks for your help, Mitch
> Sent via Deja.com http://www.***.com/
> Before you buy.
