Passing multiple rows to a function

Question

I was looking into XLeratorDB's XIRR function and I see they have an example like this:

SELECT wct.XIRR(cf_amt, cf_date, NULL) as IRR
FROM (VALUES
 ('01/01/2010',8039),
 ('01/01/2010',-8039),
 ('02/01/2010',9333),
 ('02/01/2010',-9333),
 ('03/01/2010',9783),
 ('03/01/2010',-9783),
 ('04/01/2010',8373),
 ('04/01/2010',-8373),
 ('05/01/2010',7493),
 ('05/01/2010',-7493),
 ('06/01/2010',9738),
 ('06/01/2010',-9738),
 ('07/01/2010',6382),
 ('07/01/2010',-6382),
 ('08/01/2010',8627),
 ('08/01/2010',-8627),
 ('09/01/2010',6871),
 ('09/01/2010',-6871),
 ('10/01/2010',9091),
 ('10/01/2010',-9091),
 ('11/01/2010',9153),
 ('11/01/2010',-9153),
 ('12/01/2010',7253),
 ('12/01/2010',-7253),
 ('01/01/2011',8197)
 ) n(cf_date, cf_amt)
This produces the following result.
 IRR
----------------------
 NULL
(1 row(s) affected)

I didn't know that it was possible to send a table to a function in this way. I'm wondering how that is done in SQL Server.

Note: I'm not asking about how to do an XIRR calculation, rather about the syntax that can let you pass the result of a select like that to a function without first inserting it into a table variable and passing that variable.

Answer 1

That particular function is a SQLCLR User-Defined Aggregrate (UDA). It is being called multiple times, once per each output row (pre-HAVING clause, if one is present). A UDA maintains state between executions so in some respects it does access all rows within a particular group, but it only gets one row at a time, and needs to store info for future calls.

Otherwise, SQLCLR functions cannot accept multiple rows. T-SQL functions, however, do have the ability to accept multiple rows via a Table-Valued Parameter (TVP).