whereclause - colname in (null,"s") 
Author Message
 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.



Fri, 02 May 2003 03:00:00 GMT
 whereclause - colname in (null,"s")

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.deja.com/
Before you buy.



Sat, 03 May 2003 10:21:05 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. max of ("...","...","..")

2. ALTER field from "NOT NULL" to "NULL"

3. "IS NOT NULL" != "NOT NULL"

4. "= null" & "is null"

5. Query result show "NULL"

6. Treatment of "IS NULL"

7. How to make a "decimal"-field to an "integer"-field

8. Unable to delete "null" entries

9. User "null" authentication error

10. Set "accept null" to all columns

11. Changing column to "not null"

12. how to suppress "warning null value..."


 
Powered by phpBB® Forum Software