Database Table Update Error on NULL Value

Question

Hello everyone,

I have a two tables. One table is actually populated by daily events while the other is just a summary of the other table. I have a stored procedure which deletes the values stored in the Daily Table. Now every after delete, the Summary table should also be updated such that the SUM will now be updated less the value of the one deleted.

My problem is when the Daily table has been emptied, then automatically summing it up yields a value of NULL (since no entries are found inside it). The application then crashes since my Summary Table should not contain NULL Values.

If it is possible, how can I create a procedure that would make the value 0 if the SUM results to NULL?

Thank you very much!

SAMPLE QUERY:

DELETE FROM Daily WHERE DailyID = @DailyID

SELECT @TotalA = SUM(o.OutA), @TotalB = SUM(o.OutB), @TotalC = SUM(o.OutB)
FROM Daily 
WHERE Day = @Day

UPDATE Summary 
SET MyA = @TotalA, MyB = @TotalB, MyC = @TotalC
WHERE SummaryID = @SummaryID

From my Query given above, let's say I have a total of 10 entries inside my Daily Table. 2 are Wednesday Entries, 3 are Friday, and 5 are Saturday Entries. So basically, my Summary Table will have three entries - a summary of each Wednesday, Saturday, and Friday.

When I ask my procedure to delete all the Wednesday Entries, then there will be 8 entries left (no wednesday). And so I have to update my Summary entry saying that all the wednesday entries have been deleted. However, I don't want my Summary Table to forget that he had a Wednesday. So instead, I want him to retain the Wednesday entry with Total Summary Values of zeroes (0).

Answer 1

You can use COALESCE to replace NULL by a value:

SELECT COALESCE( your_column, 0 )
FROM your_table

It takes a list of arguments, and returns the first one that is NOT NULL.

---

EDIT: With your example you could do something like that:

UPDATE Summary
SET MyA = Coalesce( @TotalA, 0 ),
    MyB = Coalesce( @TotalB, 0 ),
    MyC = Coalesce( @TotalC, 0 )
WHERE SummaryID = @SummaryID

Answer 2

Is this on SQL server try ISNULL(SUM(Column),0).