In a function I have a temporary table variable
DECLARE TABLE
(
d DATE
)
which contains some sorted dates:
DDate
2016-06-15
2016-05-11
2016-04-15
2016-03-15
2016-02-16
2016-01-14
I have to call a table-valued function
MyCompare(d1 DATE, d2 DATE)
in a loop for each pair (d, next d) until the returned table is empty. The function returns the current value of d. How can I achieve it?
you use query like this, add WHERE clause with condition you want to check on the function return data
DECLARE @t TABLE
(
d DATE
)
INSERT INTO @t
VALUES
('2016-06-15')
,('2016-05-11')
,('2016-04-15')
,('2016-03-15')
,('2016-02-16')
,('2016-01-14')
SELECT t.*,fn.*
FROM
(
SELECT *,
LEAD(d) OVER (ORDER BY d ) as NextDate
FROM @t
) AS t
OUTER APPLY dbo.MyCompare(d, NextDate ) as fn
--WHERE fn.columnname IS NOT NULL
LEAD analytical function works with SQL Server 2012 or later. If you are using earlier versions try below.
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER( ORDER BY d ) AS RowNo
FROM @t
)
SELECT c.d, c1.d AS NextDate, fn.*
FROM CTE c
LEFT JOIN CTE c1 ON c1.RowNo = c.RowNo + 1
OUTER APPLY dbo.MyCompare(c.d, c1.d ) as fn
--WHERE fn.columnname IS NOT NULL
SELECTstatement, the same way you do with any other function. If you want a TVF to get parameters from another table, use INNER JOIN or CROSS APPLY.MyCompare(d1 DATE, d2 DATE)really it returns table