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.

Solomon Rutzky · Accepted Answer · 2017-08-10 22:42:07Z

4

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).

answered Aug 10, 2017 at 22:42

Solomon Rutzky

70.4k8 gold badges162 silver badges309 bronze badges

Add a comment |

Stack Exchange Network

Passing multiple rows to a function

1 Answer 1

Your Answer

Hot Network Questions

Passing multiple rows to a function

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions