reports error due to stored procedure having null variables in calculation

Question

reports error due to stored procedure having null variables in calculation

Guzman-658444

Old Hand

Points: 347
More actions
February 29, 2008 at 9:38 am

#187261

I just took up this existing project (ASP with SQL Server 2005).
A few reports blow up as a few values in stored procedures are NULL.
For instance, Avg([TravelCost]) As AvgTravel.
Count([TravelCost]) As TravelCount
TravelCost has NULL values on many records.
How can I check for IS NULL with these fields?
Any help is appreciated.
Thanks
Arun

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

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

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Hi,

You can use the ISNULL function within your stored procedure(s) to check for nulls and replace if they exists or at the report level, but I would recommend doing this at the database level. Ideally, you may want to look at possibly having a default value on affected fields, but this will require speaking with the DBA team, as its a major change.

Thank you,

Phillip Cox

MCITP - DBAdmin

TraderSam SSCrazy Points: 2931 More actions · Answer 2

It will also depend on how you want to use the records that contains nulls.

If TravelCost = null is the same as having TravelCost = 0, then you can use that record using the 'ISNULL(columnname, 0)' on the column in the select.

If TravelCost = null is NOT the same as having TravelCost = 0, then you probably want to eliminate that row from being used in the AVERAGE by using 'WHERE TravelCost IS NOT NULL.

When you AVERAGE, every value included in the AVERAGE will change the end result. By averaging zeros in, the overall average will decrease. This is OK if the null/zeros are valid records. But if they are not valid records, averaging in nulls as zeros will skew the data incorrectly.

Here is an example...suppose you want the average grade of all the students in the class. Some students were absent on days when the tests were given, so you don't have a grade for those people (essentially null...incomplete data). If you treat the null as zero (which is not the case if the students can make-up the test and get a real grade), then the AVERAGE will be less than it really is.

Now, if the students not taking the test (null data) do not get to make-up the test which means that the null is the same as zero, then you would want to average these values in converting the nulls to zero.

It all depends on the context of what you are doing with the data.

Hope this helps!

If it was easy, everybody would be doing it!;)

Guzman-658444 Old Hand Points: 347 More actions · Answer 3

Thanks for the suggestions.

I will try them out.

Arun