1

How can one create a custom aggregation function for MS SQL with variable number of arguments?

public void Accumulate(SqlString value, params SqlInt32 [] ids)
{
    // check for compatibility with the previous ids and sum if compatible
}

When I deploy the assembly, I get the following error:

SqlClrDeploy:
  Beginning deployment of assembly My.SQLExtensions.dll to server srvname : dbname
  The following error might appear if you deploy a SQL CLR project that was built
     for a version of the .NET Framework that is incompatible with the target
     instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly
     failed because assembly failed verification". To resolve this issue, 
     open the properties for the project, and change the .NET Framework version.
path\to\assembly.dll : Deploy error : Could not create a type for parameter
     System.Data.SqlTypes.SqlInt32[] ids

If it is not possible to use custom aggregations with varargs, what would be a workround?

The purpose of this custom aggregation is to calculate totals, if all the ids remain the same in the group (it returns null, if any of ids changes).

Update: Finally I chose to do it in SQL, without custom aggregation. For example:

SELECT a, b, c, 
       CASE WHEN COUNT(*) = 1 OR 
                 COUNT(id) = 0 OR 
                 (VAR(id) = 0 AND (COUNT(*) - COUNT(id) = 0))
            THEN SUM(value) 
       ELSE NULL END 
FROM TABLE GROUP BY primaryID
khachik
  • 28,112
  • 9
  • 59
  • 94
  • Out of curiosity, what does custom aggregate do? Running totals? – gbn Jan 18 '12 at 14:15
  • @gbn, yes, if ids are the same, and returing `null` if they aren't. – khachik Jan 18 '12 at 14:17
  • I'd suggest you may be able to do it in SQL then. See http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver or http://stackoverflow.com/questions/2118829/performant-way-to-get-the-maximum-value-of-a-running-total-in-tsql – gbn Jan 18 '12 at 14:20
  • did you look into table parameters? – KM. Jan 18 '12 at 15:24
  • @KM., could you please elaborate more? Where and how do I need to look into table parameters and what parameters? – khachik Jan 18 '12 at 19:48
  • see [Table-Valued Parameters (Database Engine)](msdn.microsoft.com/en-us/library/bb510489.aspx) – KM. Jan 19 '12 at 13:32
  • Here is a [very well-written explanation](http://www.sommarskog.se/arrays-in-sql-2008.html) of table-valued parameters (TVP) – kuujinbo Jan 19 '12 at 19:24
  • @kuujinbo, nice article, thank you! – khachik Jan 20 '12 at 06:13
  • @khachik - If you are able to post your code either here or somewhere else (since it's probably pretty long) like pastebin, that would be nice. Your question about how to implement this using CLR is interesting and I would like to see what you were trying :) – kuujinbo Jan 20 '12 at 09:40
  • @kuujinbo, I solved my problem without user-defined aggregations, see the update. I will try to implement that UDA later, thank you and others for your help. – khachik Jan 20 '12 at 10:56

0 Answers0