Re: Sybase ASE OLE DB Provider adCmdStoredProc bug
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 1 Dec 2007 16:02:24 +0000 (UTC)
Jose Luis (jose.luis.fdez.diaz@xxxxxxxxx) writes:
When I exec a sybase procedure from Visual Basic, the "NULL
comparation" (if xxx = NULL) doens't run. However if the sintax is "if
xxx is NULL" it runs fine.
I don't really know what you mean with "doesn't run", but I assume
that if you have:
IF @x = NULL
PRINT '@x is = NULL'
ELSE
PRINT '@x is not known to be = NULL'
This prints the latter, no matter @x is NULL or not.
This is the expected behaviour in SQL. All comparisons with NULL yields
the value UNKNOWN. NULL stands for "unknown value", and if both @x and @y
are NULL, they may not be the same unknown value. Therefore, for all tests
you should use @x IS [NOT] NULL.
Originally, Sybase did not adhere to this, and if you run the above in
Sybase 4.x it will indeed print '@x is = NULL' if @x is NULL. Some
time after Microsoft had separated from Sybase, they add the SET option
ANSI_NULLS, and since SQL 7 this option is ON by default in most contexts.
I have not worked with Sybase for many years, but I have occasionally
glanced in their documentation, and I seem to recall that they too have
a ANSI_NULLS SET option.
In any case @x IS [NOT] NULL is what you should use with any DB engine.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- Next by Date: Re: SQL state=08S01
- Next by thread: Re: SQL state=08S01
- Index(es):
Relevant Pages
|
