help with isnull()

  • Hello All,

    I have an issue with isnull. basically what iam trying to do is:

    where

    col1 = isnull(@variable,col1)

    i.e when @variable is null then condition on the col1 should be igonored and query should return all the values, even those where col1 is null.

    but it is not returning those where col1 is null.

    Please help me resolve this issue.

    I appreciate your help.

    Thanks,

    Sandy

  • http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx

    Thats a good site and will try to explain the answer to you.

  • Use COALESCE. It returns the first NON-NULL value in the list.

    WHERE

    col1 = COALESCE(@variable, col1)

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (11/29/2007)


    Use COALESCE. It returns the first NON-NULL value in the list.

    WHERE

    col1 = COALESCE(@variable, col1)

    Sorry Jason - gotta part ways with ya here...

    Sandy is looking to return items using something like

    where col1=@variable OR col1 is null

    The COALESCE syntax above would never return anything null by design......

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (11/29/2007)


    Jason Selburg (11/29/2007)


    Use COALESCE. It returns the first NON-NULL value in the list.

    WHERE

    col1 = COALESCE(@variable, col1)

    Sorry Jason - gotta part ways with ya here...

    Sandy is looking to return items using something like

    where col1=@variable OR col1 is null

    The COALESCE syntax above would never return anything null by design......

    Actually, I think it's a mixture of both. Yours will not return all rows if the @variable is null since NULL never equals NULL, and mine will not return rows where col1 is null.

    So I think what would be needed is ...'

    WHERE

    ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))

    Anywho, gotta run for a bit......

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Agreed... a 2 operand COALESCE is identical to ISNULL and has the same "problem" with finding nulls, in this case.

    To expound upon the point that Matt makes, consider the following code...

    where

    col1 = isnull(@variable,col1)

    If both @variable and col1 are null, the WHERE clause evaluates as follows...

    where

    col1= NULL

    ... and, unless you made the serious mistake of how the server handles null, you cannot compare against a null with signs of equality. It just doesn't work.

    Matt's code of...

    WHERE Col1 = @variable or col1 IS NULL

    ... is likely the correct way to do things here (can't see the rest of the code so not 100% sure).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/29/2007)


    ... and, unless you made the serious mistake of how the server handles null, you cannot compare against a null with signs of equality. It just doesn't work.

    Matt's code of...

    WHERE Col1 = @variable or col1 IS NULL

    ... is likely the correct way to do things here (can't see the rest of the code so not 100% sure).

    But unfortunately this doesn't answer the posters original need ...

    when @variable is null then condition on the col1 should be ignored and query should return all the values, even those where col1 is null.

    So if a NULL @variable is put in you won't return any records other than NULLS. Likewise, no matter what variable is passed, you will always get NULLs and I don't think that is what Susan wants

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jeff was correct most of the way, this way you can get all the results back:

    where col1 = isnull(@variable,col1) or (@variable is null and col1 is null)

    I believe that should work, try it out.

  • Jason - exhibit A:

    sandygem2k (11/29/2007)query should return all the values, even those where col1 is null.

    but it is not returning those where col1 is null.

    So - the syntax should be the same as NO where clause when col1 is null....

    Now - if you do want to do this using ISNULL, try

    WHERE ISNULL(col1,@variable)=@variable

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hello All,

    Thank You all for your suggestions...

    What I am looking for is when @variable is null then the condition on the col should be ignored.

    I am using this in Reporting Services...

    So I am came up with this ..

    (case @test-2 when 'ALL' then 'ALL' else col1 end )= (case @test-2 when 'ALL' then 'ALL' else @test-2 end )

    @test-2 should be ALL when the user does nt want to give it any value...

  • FIRST: I'm not attacking anyone, I'm just convinced that my second answer (Only after Matt pointed out an error) will work.

    SECOND: Ravi's code will work as well. 😀

    Matt, I think you've misunderstood the question. Susan only wants all of the records of col1 when the @variable is null, not when col1 is null.

    sandygem2k (11/29/2007)when @variable is null then condition on the col1 should be igonred and query should return all the values, even those where col1 is null.

    but it is not returning those where col1 is null.

    TEST DATA ....

    Using Matt's first suggestion

    DECLARE @table TABLE (col1 VARCHAR(10))

    DECLARE @variable VARCHAR(10)

    -- initialize the variables

    INSERT @table

    SELECT 'a' UNION ALL

    SELECT 'a' UNION ALL

    SELECT 'b' UNION

    SELECT NULL

    SET @variable = 'a'

    SELECT col1

    FROM @table

    WHERE col1=@variable OR col1 is null

    erroneously returns the null record ...

    col1

    ----------

    NULL

    a[/B]

    AND fails to return ALL records if @variable is NULL ...

    SET @variable = NULL

    SELECT col1

    FROM @table

    WHERE col1=@variable OR col1 is NULL

    Incorrectly returns ...

    col1

    ----------

    NULL[/B]


    Using my second attempt, after Matt pointed out an issue ...

    SET @variable = NULL

    SELECT col1

    FROM @table

    WHERE

    ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))

    Correctly returns ...

    col1

    ----------

    NULL

    a

    b

    AND ...

    SET @variable = 'a'

    SELECT col1

    FROM @table

    WHERE

    ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))

    Correctly returns ...

    col1

    ----------

    a

    Now Matt's second suggestion of ...

    SET @variable = 'a'

    SELECT col1

    FROM @table

    WHERE ISNULL(col1,@variable)=@variable

    Incorrectly returns the record with the NULL value ...

    col1

    ----------

    NULL

    a


    OH, And Sandy seems to have found her own solution.

    Again, I'm just confident and not attacking. Now just imagine if I am actually wrong... LOL

    What do you think now Jeff ... :Whistling:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • welll...then...good job!!!!!

    :hehe::cool::w00t:;):P:D:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Heh... Jason, I'm sitting here reading where you wrote...

    So if a NULL @variable is put in you won't return any records other than NULLS. Likewise, no matter what variable is passed, you will always get NULLs and I don't think that is what Susan wants

    ... and I'm thinking, "Yeah, he's right... I should'a had more coffee before I answered that".

    It funny how a couple of us have gotten to the point where we preface things with "I'm NOT attacking anyone". Show's we got a couple of WTF-battle-scars 😛

    What do you think now Jeff ...

    I'm thinking I was wrong... that I needed more coffee. 😀 And I think you did a heck of a nice job with the test code to prove it! :w00t: Well done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, Matt and whomever else is reading this ....

    I think all that happened here is something that I KNOW I have been guilty of ... "Answering a question without really understanding all of it". I've done it a few times and I always end up with egg on my face. Heck, I did it on this post with my first reply. In the end I think it's the nature of our business. We are constantly (at least I am) expected to solve problems as quickly as possible. It's very easy for things like this to happen in that situation and difficult to slow down sometimes.

    All in all, I'm glad we could work through this without getting feathers ruffled 🙂

    One of the things I'm taking out of this thread is that I know of at least two more people that I can work on issues with professionally.

    Thanks guys!

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Heh... thanks for that, Jason. Hope a couple of other folks take heed of that little lesson...

    In the meantime, what's the best way for me to get this dried egg out of my beard without cutting it? 😛 Oh, I know... I'll go "soak my head" for a while 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply