December 14, 2004 at 2:09 pm
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?
December 14, 2004 at 2:22 pm
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
December 15, 2004 at 6:06 am
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
December 15, 2004 at 12:13 pm
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