SQL Server T-SQL – How to Pass Multiple Rows to a Function

sql servert-sql

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.

Best Answer

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

Related Solutions

Sql-server – Help with tricky update statement

The performance impact from multiple logical reads could be a result of your UDF.

Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

To replicate your scenario, I executed the following query:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
             Measurement varchar(max), -- Grams, Kilograms, Liters, etc
             MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
   @Items TABLE(ItemType varchar(max),   
            Quantity float,  
            Measurement varchar(max),   
            ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L'); 

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');

Afterwards, I complied and executed the following update statement:

WITH Items_CTE AS
   (
        SELECT A.ItemType, 
               A.Quantity,
               A.Measurement,  
               (SELECT TOP 1 M.Measurement FROM @Measurements M
                JOIN @Items C ON C.Measurement = M.Measurement
                WHERE M.MeasurementType = B.MeasurementType
                AND  C.ItemType = A.ItemType
                 ORDER BY Precidence)ToMeasurement
       FROM @Items A
       JOIN @Measurements B
       ON A.Measurement = B.Measurement
    )  
UPDATE @Items  
SET ToMeasurement = cte.ToMeasurement  
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

The UPDATE statement uses a CTE and the results were as follows:

ItemType | Quantity | Measurement | ToMeasurement  
Widget   |    1     |    G        |    KG  
Widget   |    1     |    KG       |    KG  
Widget   |    1     |    ML       |    L  
Widget   |    1     |    L        |    L

T-sql – Conditional Where Statement Involving a NULL value

Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:

WHERE 
(
    [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status 
    OR 
    ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)

You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to read and understand the OR syntax above, so would prefer to use that.

WHERE 
(
    CASE WHEN @Status IS NULL AND [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL THEN 1
       WHEN [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status THEN 1
       ELSE 0 END
) = 1

Best Answer

Related Solutions

Sql-server – Help with tricky update statement

T-sql – Conditional Where Statement Involving a NULL value

Related Question