Using a Table Valued Function inside a Recursive CTE

Question

I am trying to use a recursive CTE to combine multiple calls to a table valued function.

I have a fiddle at: https://dbfiddle.uk/kktqHivz

I have a table called sales:

create table sales (
    date date,
    total decimal(6,2)
);

The function ds(date) selects all the sales for a date:

create function ds(@date date) returns table as
return select date, total from sales where date=@date;

I thought I could put them into a recursive CTE as follows:

with cte as (
    select cast('2023-01-01' as date) as n, *
    from ds('2023-01-01')
    union all
    select dateadd(day,1,n),d.*
    from cte, ds(n) as d    --  can’t use ds(n) or ds(cte.n)
    where n<'2023-01-05'
)
select * from cte order by n option(maxrecursion 100);

but the message I get is:

Invalid column name 'n'

I’m stuck at this point. The n is a column from the CTE, and normally you can us it in the recursive member, as you see with the SELECT clause. Somehow I can’t use it as an argument for the ds() TVF.

Is there a way I can use the generated date n in the table valued function?

Answer 1

As mentioned by the other answer, you would need cross apply.

But the logic of your CTE is incorrect. You need to generate the dates first, then apply the function

with cte as (
    select cast('2023-01-01' as date) as n

    union all

    select dateadd(day, 1, n)
    from cte
    where n < '2023-01-05'
)
select *
from cte
cross apply ds(cte.n)
order by n
option(maxrecursion 100);

Having said that, you should probably just use a straight range lookup

select
  date,
  total
from sales
where date >= @date
  and date < '2023-01-05';