Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from

Question

I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue and theDate. Now assume I have a large table, called TableOfValue of values like so:

Values	Date
1673	'2021-05-23'
1420	'2021-05-24'
-3273	'2021-05-25'
7231	'2021-05-26'
2331	'2021-05-27'

What I want to do is call my f_doCalc with the dates and values from all previous dates. The first row would be called with a @MyUserDefinedTableType containing

theValue	theDate
1673	'2021-05-23'

The second with a @MyUserDefinedTableType containing

theValue	theDate
1673	'2021-05-23'
1420	'2021-05-24'

and so on.

Is this somehow possible to accomplish without using a WHILE loop? Such as of the form

SELECT Values, Date, f_doCalc( ... )
FROM TableOfValue

---

Currently, I'm running a while loop and inserting the relevant rows into the table type, running the function, and updating the main table, for each iteration. I'm wondering if there's a better way.

For the above table example, 5 calculations done by f_doCalc() on first day 1 and corresponding value, then day 1 and day 2 with corresponding values, then day 1, day 2, day 3 etc... You can imagine f_doCalc() produces a running sum for example (in reality it's a much more complicated calculation).

I need to calculate values corresponding to each day. The calculation done is IRR (internal rate of return) which is an iterative procedure to find a value. I need to do this calculation each day with all the previous data available from the days up until that point.

• Charlieface: You say it involves IRR, perhaps stackoverflow.com/questions/12074638/…

Answer 1

Consider moving away from custom table data types forever!! We have json and xml for passing tuples around. And we can do that without any sort of schema binding.

CREATE OR ALTER FUNCTION dbo.doCalc(@theArgument varchar(max)) 
RETURNS INT
AS
BEGIN
 DECLARE @returnValue int;
 SELECT @returnValue = AVG([values])
 FROM OPENJSON(@theArgument, N'$')
 WITH (
 [date] date N'$.date'
 , [values] int N'$.values'
 )
 RETURN @returnValue;
END
GO
CREATE TABLE #theTable ([date] date PRIMARY KEY, [values] int NOT NULL)
INSERT #theTable([date], [values])
VALUES 
 ('2021-05-23', 1673)
 , ('2021-05-24', 1420)
 , ('2021-05-25', -3273)
 , ('2021-05-26', 7231)
 , ('2021-05-27', 2331)
SELECT q.[date], q.[values], q.[tuplesJson], dbo.doCalc(q.[tuplesJson]) as doCalc
FROM (
 SELECT 
 t.[date]
 , t.[values]
 , (
 SELECT 
 w.[date]
 , w.[values]
 FROM #theTable as w
 WHERE t.[date] >= w.[date]
 ORDER BY w.[date]
 FOR JSON PATH
 ) as tuplesJson
 FROM #theTable as t
) as q
/*
date values tuplesJson doCalc
---------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- 
2021年05月23日 1673 [{"date":"2021年05月23日","values":1673}] 1673
2021年05月24日 1420 [{"date":"2021年05月23日","values":1673},{"date":"2021年05月24日","values":1420}] 1546
2021年05月25日 -3273 [{"date":"2021年05月23日","values":1673},{"date":"2021年05月24日","values":1420},{"date":"2021年05月25日","values":-3273}] -60
2021年05月26日 7231 [{"date":"2021年05月23日","values":1673},{"date":"2021年05月24日","values":1420},{"date":"2021年05月25日","values":-3273},{"date":"2021年05月26日","values":7231}] 1762
2021年05月27日 2331 [{"date":"2021年05月23日","values":1673},{"date":"2021年05月24日","values":1420},{"date":"2021年05月25日","values":-3273},{"date":"2021年05月26日","values":7231},{"date":"2021年05月27日","values":2331}] 1876
*/

• Oscar: That looks very interesting! Will definitely give that a try and see if I can fit to my needs. Thank you!

Answer 2

I'm pretty sure there is no way to create or manipulate table-type values in SELECT statements which is what you seem to want.

For the single row example you could create a wrapper function which takes the values you want in the table variable as individual arguments, creates a table variable containing them, then calls the existing function and forwards on the result to the caller. This would not be likely to be efficient though.

For the rolling list needed for the second and subsequent rows that would not directly work. You could have extra arguments for the function defining the start and end range from which to construct the content of the table variable, and use window functions to derive those values to pass in.

I expect this is not a good idea though, in terms of efficiency and potential locking issues. Perhaps you should ask a new question describing what you actually need the process to achieve overall? There may be a simpler way to get the result you are looking for.

Answer 3

Maybe the following setup could solve the issue. If TableOfValue is a global table, you can use the Date column as a parameter to the function to limit the rows included in the calculation.

/** SET UP **/
CREATE TABLE TableOfValue
(
 VALUE int,
 Date datetime
)
 
INSERT INTO dbo.TableOfValue VALUES (1673, '2021-05-23');
INSERT INTO dbo.TableOfValue VALUES (1420, '2021-05-24');
INSERT INTO dbo.TableOfValue VALUES (-3273, '2021-05-25');
INSERT INTO dbo.TableOfValue VALUES (7231, '2021-05-26');
INSERT INTO dbo.TableOfValue VALUES (2331, '2021-05-27');
SELECT * FROM TableOfValue
-- implementation of the f_doCalc function
-- use the date to limit the range of rows for computation
CREATE FUNCTION f_doCalc(
 @date datetime 
)
RETURNS int
AS
BEGIN
 DECLARE @calculatedVal AS INT;
 SELECT @calculatedVal = SUM(VALUE) -- your calculation is different
 FROM dbo.TableOfValue 
 WHERE date<=@date 
 RETURN @calculatedVal
END
GO
 
/** MAIN QUERY **/
SELECT Value,
 Date,
 dbo.f_doCalc(tov.Date) AS calculation
FROM TableOfValue tov

• LoztInSpace: This is what I would do!