2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.