When does a null not equal a null?

  • I'm trying to create a stored procedure to selectively update or insert to a table.  The origin of the data is a spreadhseet containing the periods of the (current) year and the user populates them as the year progresses.  This is loaded into a staging table and then processed by the stored procedure.

    I need to permit them to amend historical figures, as occasionally reports arrive late but still need to be measured.  To avoid duplication, I've come up with a routine for each of the periods that checks if the row already exists in the database.  If it doesn't, we insert, otherwise we update.

    It's not working.

    The problem is that the rows are populated unevenly.  The columns are year, period, category, sub-category, quantity.  Not every row has a sub-category, so there are nulls.  Some categories have several sub-categories.

    The code is:

    if (select count(*) from table

     where year = @year

     and period = @period

     and category = @category

     and SubCategory = @Subcategory)=0

     insert.......

    else

     update.....

     

    When the sub-category is null, it inserts instead of updating.

    Anybody got any ideas?

  • Paul,

    As your subject heading suggested, the problem is with nulls and equality.  With standard defaults for SQL SERVER (ANSI_NULLS on) it is neither true that null = null or that null <> null.  Of course, null = 'some literal' is always false.

    In your case, the probelm is in comparing the NULL value in Table.Subcategory with the value you input.  No rows are matched ("not true" that null = @SubCategory), so you insert.

    If you pass in a NULL value for @SubCategory, one method is to do something like this:

    if (select count(*) from table

     where year = @year

     and period = @period

     and category = @category

     and ISNULL(SubCategory,'Some value') = ISNULL(@Subcategory, 'Some Value'))=0

     insert.......

    else

     update.....

    where the literal 'Some value' is not otherwise a valid piece of data in Table. This will allow "null" to match "null" and you will update instead of insert.

    If this doesn't work for your case, post back and we'll go from there.

    Scott Thornburg

    Volt Information Sciences

     

     

     

  • I assume that @Subcategory contains a specific subcategory for which you are searching.

    How about:

    if (select count(*) from table

     where year = @year

     and period = @period

     and category = @category

     and ( SubCategory = @Subcategory OR SubCategory IS NULL ))=0

  • And Adding to mkeast : instead of aggregating you should use existence check!

    Like:

    if exists (select * from table

     where year = @year

     and period = @period

     and category = @category

     and ( SubCategory = @Subcategory OR SubCategory IS NULL ))

    ...

    HTH


    * Noel

Viewing 4 posts - 1 through 4 (of 4 total)

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