Calculated column not working

Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much.

Dave Roda

Change the stored proc so that it only performs the calulation if both columns contain positive integers. There's no point calculating an average if either column has no value.

If ((TotalRating > 0 And TotalRating Is Not Null) AND (TotalVotes > 0 AND TotalVotes Is Not Null))

Update Tablename Set AverageRating = TotalRating/TotalVotes...

|||

Hi Dave,

Based on your description, I understand that you're getting null values in AverageRating if one of the other columns is null.

Actually, this is by design. In this case, you have to check for nulls using ISNULL in your calculate stored procedure.

HTH. If anything is unclear, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||This is a very helpful post thank you. I was wondering if there was a way to use conditionals in a stored procedure. Could you please give me a quick example? I understand the concept i will check if the cells values are null and set the value to one instead of incrementing null+1. That would work but it would also be just as easy to set the default value of the columns to 0 but i cannot figure out how to do this. Answers to either of those questions would be greatly appreciated. Thanks so much!|||

I'm not sure what's causing your problem with the default value. You shouldn't have any problem setting the default for a number column to 0; Can't you change this in table design mode (if sql doesn't work)? If you're using SQL express, you candownload a SQL Enterprise Manager-like interface to access and change database info, if you are more familiar with SQL Server than Server Explorer (again, I don't know what you're using right now).

As for conditionals:

DECLARE @.TotalintSELECT @.Total =CASEWHEN TotalVotesisnullTHEN 0WHEN TotalVotes < 0THEN 0ELSE TotalVotesFROM VoteTableWhere ItemID = 10Return @.Total

This statement dynamically assigns a value to a variable. If doing a view, etc., you could leave out the @.Total =, and the results would come through like a regular query but with your altered values.