1

I'm looking to add some code to my TSQL arsenal to defend against performing aggregations in SQL when the data in a column is null. Ideally there would be a SUM_NN (for sum no null), in sql server which would raiserror if any of the values were null.

Since you can't raiserror from a UDF, the only way I could think of doing it looked like this, though I don't like this solution:

CREATE FUNCTION dbo.NULL_TEST_F(@arg FLOAT)
RETURNS FLOAT
AS
BEGIN
    IF(@arg IS NULL)
        SELECT 1/0
    RETURN @arg
END

Note: I think this is stupid solution, but I've gotten burned way too many times when I'm missing data. Also, we're using SQL Server 2005, but I'm open to 2008 and 2012 solutions. Additionally, I'd like to know how other databases deal with this type of issue.

Cezar
  • 55,636
  • 19
  • 86
  • 87
bpeikes
  • 3,495
  • 9
  • 42
  • 80

2 Answers2

3

This was my final solution:

CREATE FUNCTION [dbo].[NullTest_F]
(
    @input FLOAT,
    @message VARCHAR(100)
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @test INT
    IF(@input IS NULL)
        SELECT @test = CAST(@message AS INT)
    RETURN @input
END

I can then embed this with a useful error message when running aggregate functions. Example:

CREATE TABLE Data (
    Date DATETIME,
    DataPoint FLOAT
)

INSERT INTO Data (Date, DataPoint) VALUES ('2012-03-01', 4)
INSERT INTO Data (Date, DataPoint) VALUES ('2012-03-02', 6)

SELECT SUM(NullTest_F(DataPoint, 'Missing data at' + CONVERT(VARCHAR(10), Data))
FROM Data
bpeikes
  • 3,495
  • 9
  • 42
  • 80
2

Maybe this one will help: https://stackoverflow.com/a/4681815/1371070

You could create a function like suggested in the answer linked above and call it from your aggregate in case @arg is null.

It's still the same strategy overall but It's a better error to throw than divide-by-zero, I guess.

Community
  • 1
  • 1
Cezar
  • 55,636
  • 19
  • 86
  • 87