Hi All,
I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.
When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?
---------------
set ANSI_NULLS off
drop table #TestNull
create table #TestNull (Field1 varchar(10), Field2 varchar(10))
insert into #TestNull values (1, null)
insert into #TestNull values (1,1)
declare @TestVar varchar(10)
select @TestVar = Null
select * from #TestNull where Field1 = @TestVar
---------------
Thanks in advance,
Saul 4 7889
Your SQL Server select should look like this:
SELECT * FROM #TextNull WHERE Field1 IS NULL
CJ
"Saul Margolis" <sa***********@hotmail.com> wrote in message
news:c2**************************@posting.google.c om... Hi All,
I'm converting some stored procs from Sybase to SQL Server, and I'm having serious problems with Null comparisons.
When I run the code below on Sybase, it returns both rows. When I run it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in the code (and on the session through Query Analyzer), but it doesn't seem to make any difference. Am I missing something?
--------------- set ANSI_NULLS off
drop table #TestNull
create table #TestNull (Field1 varchar(10), Field2 varchar(10))
insert into #TestNull values (1, null) insert into #TestNull values (1,1)
declare @TestVar varchar(10)
select @TestVar = Null
select * from #TestNull where Field1 = @TestVar ---------------
Thanks in advance, Saul
"Saul Margolis" <sa***********@hotmail.com> wrote in message
news:c2**************************@posting.google.c om... Hi All,
I'm converting some stored procs from Sybase to SQL Server, and I'm having serious problems with Null comparisons.
When I run the code below on Sybase, it returns both rows. When I run it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in the code (and on the session through Query Analyzer), but it doesn't seem to make any difference. Am I missing something?
--------------- set ANSI_NULLS off
drop table #TestNull
create table #TestNull (Field1 varchar(10), Field2 varchar(10))
insert into #TestNull values (1, null) insert into #TestNull values (1,1)
declare @TestVar varchar(10)
select @TestVar = Null
select * from #TestNull where Field1 = @TestVar ---------------
Thanks in advance, Saul
I guess you mean WHERE Field2 = @TestVar ? That will return the (1, NULL)
row.
See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.
Simon
Saul Margolis (sa***********@hotmail.com) writes: I'm converting some stored procs from Sybase to SQL Server, and I'm having serious problems with Null comparisons.
When I run the code below on Sybase, it returns both rows. When I run it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in the code (and on the session through Query Analyzer), but it doesn't seem to make any difference. Am I missing something?
--------------- set ANSI_NULLS off
drop table #TestNull
create table #TestNull (Field1 varchar(10), Field2 varchar(10))
insert into #TestNull values (1, null) insert into #TestNull values (1,1)
declare @TestVar varchar(10)
select @TestVar = Null
select * from #TestNull where Field1 = @TestVar ---------------
I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.
Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:
o Indexed views.
o Index on computed columns.
o Access to linked servers.
With ANSI_NULLS off, these features are unavailable.
Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.
Also, you may need to have a go for ANSI_NULLS to have effect.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?
Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)
That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jef S |
last post by:
------=_NextPart_000_002B_01C34718.7E9AFEA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Greetings to all,
Using a SELECT Statement, I am trying to pull...
|
by: Matt Rink |
last post by:
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
error after creating a view.
We wanted a composite unique constraint that ignored nulls, so we set
up a view using the...
|
by: A.M. de Jong |
last post by:
When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error...
|
by: Don Vaillancourt |
last post by:
What's going on with Javascript.
At the beginning there was the "undefined" value which represented an
object which really didn't exist then came the null keyword. But
yesterday I stumbled...
|
by: John Sidney-Woollett |
last post by:
Hi
I'm building a web app where changes to customer orders are logged in the
following table, and I worried about the time that it will take to locate
records that need further...
|
by: yossi.kreinin |
last post by:
Hi!
There is a system where 0x0 is a valid address, but 0xffffffff isn't.
How can null pointers be treated by a compiler (besides the typical
"solution" of still using 0x0 for "null")?
-...
|
by: fieldfallow |
last post by:
Hello all,
Before stating my question, I should mention that I'm fairly new to C.
Now, I attempted a small demo that prints out the values of C's numeric
types, both uninitialised and after...
|
by: Shwetabh |
last post by:
Hi,
My question is, is there any difference between a NULL and a Blank
(Unknown, Not Applicable) field in MS SQL or are they the same?
Awaiting your comments,
Regards
|
by: asdf |
last post by:
I got a warning from gcc for the following code
if (nextIs_.size() == NULL)
warning: NULL used in arithmetic
how can I fix this problem? It seems that I can install a patch for
gcc, but I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |