Results 1 to 9 of 9

Thread: != Null ?

  1. #1
    Join Date
    Mar 2004
    Posts
    35

    Unanswered: != Null ?

    This code is from a book:
    Code:
       SELECT @UserID = UserID FROM Accounts_Users WHERE Email = @Email
       
       IF @UserID != NULL
    	RETURN @UserID
       ELSE
    	RETURN -1
    And works fine. I've modified it like this and now it doesn't work anymore:
    Code:
       SELECT @UserID = UserID FROM Accounts_Users WHERE Nick = @Nick
    (Email and Nick are both varchars, just different size) But the @UserID parameter is not NULL, it has the correct ID (int) from the record with the Nick, e.G. 4 or 17. Strange. Anyway would it be OK if I modified the code like this:
    Code:
       IF @UserID > 0
    	RETURN @UserID
       ELSE
    	RETURN -1

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unless you monkey with settings that sane people leave alone, NOTHING equals NULL. There are reasons for this behavior that are rather complex, but they are pretty firmly rooted in the algebra that supports database theory.

    I'd strongly suggest that you use:
    Code:
    IF @UserId IS NOT NULL
    instead of the test that you posted. It should work much better!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    NULL behavior was changed with SQL Server 7.0 from ANSI_NULLS=OFF to ANSI_NULLS=ON, in order to comply with ANSI standards. The following code will demonstrate 6.5 default behavior (ANSI_NULLS OFF) by displaying "@m = null":

    set ansi_nulls off
    declare @m int
    if @m = null print '@m = null' else print '@m != null'

    If the setting is changed to ON, the result will be "@m != null"

    I also would not call it "monkeying with the settings". A good back-end developer or a DBA should know very well the meanings and the effects of this and other settings, their scope, and what they affect. This knowledge is very important simply because different front-end tools, and even different versions of the same tool use those settings differently. We all saw what happened with MDAC from 2.0 to 2.8 (I think 2.8 is already out, isn't it?) Read BOL on SET commands. It's also referenced in "What's New..." section.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Mar 2004
    Posts
    35
    Thanks Pat it works fine.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    A good back-end developer or a DBA should know very well the meanings and the effects of this and other settings, their scope, and what they affect. This knowledge is very important simply because different front-end tools, and even different versions of the same tool use those settings differently.
    Agreed, but sane people still don't mess with those settings. Once you change them to make things easier, you effectively isolate your code into running with those settings. This kind of "ghetto" mentality has led to the collapse of a number of products because they knowingly chose to insist on running with non-standard settings.

    I'm not saying that it can't be done, nor even that it shouldn't be done under very specific circumstances. My point is that writing code that demands nonstandard settings is a receipe for disaster in the long term.


    -PatP

  6. #6
    Join Date
    Mar 2004
    Posts
    35
    Thanks rdjabarov, for clearing that up a bit. I agree with you
    I like to know exactly how / why something works.

    So you are advising i use this

    Code:
    set ansi_nulls off
    in all Stored Procedures that compare against NULL

    Pat - I also agree with you - if it can be done using the standards
    then it should be done that way.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not at all. I'd recommend that you use IS NULL or IS NOT NULL whenever you need to compare against NULL. Those work with either setting just fine!

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    set ansi_nulls off...The only situation where I can see this setting will be useful would be something like this:

    --values of variables cannot be determined for whatever reason
    if @a = @b select 1 else select 0

    In this case, if both variables are NULL then 1 would be returned.

    Of course, I'd rather rewrite it this way:

    if isnull(@a, 1) = isnull(@b, 1) select 1 else select 0
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    set ansi_nulls off...The only situation where I can see this setting will be useful would be something like this:

    --values of variables cannot be determined for whatever reason
    if @a = @b select 1 else select 0

    In this case, if both variables are NULL then 1 would be returned.

    Of course, I'd rather rewrite it this way:

    if isnull(@a, 1) = isnull(@b, 1) select 1 else select 0
    Damn...the box is open now...

    OK, get a BIG matrix so you can track what setting is on and when it is off, and when you throw the switch and in what instance it applies and what instance it doesn't...

    ramble, rambvle, ramble...

    Or leave them alone...

    Big MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •