title: Cannot Pass Null Value to Custom Aggregate
body: <p>Afternoon,</p>
  
  <p>I'm writing a custom median function (without looking at existing solutions, i like the challenge), after lots of fiddling I'm most of the way there. I cannot however pass in a column that contains a null value. I'm handling this in the c# Code but it seems to be being stopped by SQL before it gets there. </p>
  
  <p>You get this error...</p>
  
  <pre><code>Msg 6569, Level 16, State 1, Line 11 'Median' failed because parameter 1 is not allowed to be null.
  </code></pre>
  
  <p>C#:</p>
  
  <pre><code> namespace SQLMedianAggregate
  {
      [System.Serializable]
      [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
     Microsoft.SqlServer.Server.Format.UserDefined,
     IsInvariantToDuplicates = false, // duplicates may change results
     IsInvariantToNulls = true,      // receiving a NULL is handled later in code 
     IsInvariantToOrder = true,       // is sorted later
     IsNullIfEmpty = true,            // if no values are given the result is null
          MaxByteSize = -1,
     Name = "Median"                 // name of the aggregate
  )]
  
      public struct Median : IBinarySerialize
      {
          public double Result { get; private set; }
  
          public bool HasValue { get; private set; }
  
          public DataTable DT_Values { get; private set; } //only exists for merge essentially
  
          public static DataTable DT_Final { get; private set; } //Need a static version so its accesible within terminate
  
          public void Init()
          {
              Result = double.NaN;
              HasValue = false;
              DT_Values = new DataTable();
              DT_Values.Columns.Add("Values", typeof(double));
              DT_Final = new DataTable();
              DT_Final.Columns.Add("Values", typeof(double));
          }
  
          public void Accumulate(double number)
          {
  
              if (double.IsNaN(number))
              {
                  //skip
              }
              else
              {
                  //add to tables
                  DataRow NR = DT_Values.NewRow();
                  NR[0] = number;
                  DT_Values.Rows.Add(NR);
                  DataRow NR2 = DT_Final.NewRow();
                  NR2[0] = number;
                  DT_Final.Rows.Add(NR2);
                  HasValue = true;
              }
          }
  
          public void Merge(Median group)
          {
              // Count the product only if the other group has values
              if (group.HasValue)
              {
                  DT_Final.Merge(group.DT_Values);
                  //DT_Final = DT_Values;
              }
          }
  
          public double Terminate()
          {
              if (DT_Final.Rows.Count == 0) //Just to handle roll up so it doesn't crash (doesnt actually work
              {
                  DataRow DR = DT_Final.NewRow();
                  DR[0] = 0;
                  DT_Final.Rows.Add(DR);
              }
              //Sort Results
              DataView DV = DT_Final.DefaultView;
              DV.Sort = "Values asc";
              DataTable DTF = new DataTable();
              DTF = DV.ToTable();
  
              ////Calculate median and submit result
              double MiddleRow = (DT_Final.Rows.Count -1.0) / 2.0;
              if (MiddleRow % 2 != 0)
              {
  
                  double upper =  (double)(DT_Final.Rows[Convert.ToInt32(Math.Ceiling(MiddleRow))]["Values"]);
                  double lower =  (double)(DT_Final.Rows[Convert.ToInt32(Math.Floor(MiddleRow))]["Values"]);
                  Result = lower + ((upper - lower) / 2);
  
              } else
              {
                  Result = (double)(DT_Final.Rows[Convert.ToInt32(MiddleRow)]["Values"]);
              }
              return Result;
          }
  
          public void Read(BinaryReader SerializationReader)
          {
              //Needed to get this working for some reason
          }
  
          public void Write(BinaryWriter SerializationWriter)
          {
              //Needed to get this working for some reason
          }
  
      }
  }
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>DROP AGGREGATE dbo.Median
  DROP ASSEMBLY MedianAggregate
  CREATE ASSEMBLY MedianAggregate
  AUTHORIZATION dbo
  FROM 'C:\Users\#######\Documents\Visual Studio 2017\Projects\SQLMedianAggregate\SQLMedianAggregate\bin\Debug\SQLMedianAggregate.dll'
  WITH PERMISSION_SET = UNSAFE;
  
  
  CREATE AGGREGATE dbo.Median (@number FLOAT) RETURNS FLOAT
  EXTERNAL NAME [MedianAggregate]."SQLMedianAggregate.Median";
  </code></pre>
  
  <p>Any ideas of what setting or code i'm missing that will allow this. I pretty much just want it to ignore nulls.</p>
  
  <p>SQL Version is SQL2008 R2 btw</p>