SQL SERVER: OPTION(MAXRECURSION n) not working with dynamic variables

Techies, This more or less works when the date range is between 1/1/-1/3. I have 2 problems I need to solve. The first is that SQL Server will not let me do this: OPTION (MAXRECURSION @recdays). The next, I am pulling results for 1/4, when I only want results up to 1/3. Any advice for getting this to work? I tried turning the query into something I could run through EXEC sp_executesql. Then I had a new problem–I couldn’t write out to #StoreXJoinDate since that spawns another session. If I convert to ##StoreXJoinDate I can’t kill it from the outer session because that session doesn’t own it.

--DEBUG
declare @beginDate date
declare @endDate date 

set @beginDate = cast('1/1/2021' as date)
set @endDate = cast('1/3/2021' as date)

declare @recdays int

select @recdays = datediff(day,@beginDate,@endDate)

;WITH Dates AS (
        SELECT
        [GenGapDate] =  @beginDate
        UNION ALL SELECT
         [GenGapDate] =  dateadd(day,1,[GenGapDate]) 
        FROM
         Dates
        WHERE
         GenGapDate <= @endDate 
  ) SELECT
   d.[GenGapDate],
   s.StoreNumber
   --s.OpenDate,
   --s.ClosedDate
  -- into #StoreXJoinDate -- drop table #StoreXJoinDate
  FROM
    Dates  as d
     cross join Dimension.Stores s
      OPTION (MAXRECURSION 3)

Here are the insert statements to build Dimension.Stores

 create table Dimension.Stores
  (StoreNumber int);

 Insert into Dimension.Stores (StoreNumber) values (1);
 Insert into Dimension.Stores (StoreNumber) values (5);
 Insert into Dimension.Stores (StoreNumber) values (7);
 Insert into Dimension.Stores (StoreNumber) values (8);
 Insert into Dimension.Stores (StoreNumber) values (9);
 Insert into Dimension.Stores (StoreNumber) values (10);
 Insert into Dimension.Stores (StoreNumber) values (11);
 Insert into Dimension.Stores (StoreNumber) values (12);
 Insert into Dimension.Stores (StoreNumber) values (13);
 Insert into Dimension.Stores (StoreNumber) values (14);

Answer

Just use that recursive CTE to load Date table. Like this:

drop table if exists Dates
Create Table Dates([Date] date primary key)

declare @beginDate date
declare @endDate date 

set @beginDate = cast('1/1/2010' as date)
set @endDate = cast('12/31/2099' as date)

declare @recdays int

select @recdays = datediff(day,@beginDate,@endDate);
WITH GenDates AS (
        SELECT
        [GenGapDate] =  @beginDate
        UNION ALL SELECT
         [GenGapDate] =  dateadd(day,1,[GenGapDate]) 
        FROM
         GenDates
        WHERE
         GenGapDate <= @endDate 
  ) 
Insert into Dates([Date])
Select [GenGapDate]
from GenDates
OPTION (MAXRECURSION 0)

Then to cross-join a date range with another table, run a query like:

select *
from Stores s
cross join Dates d
where d.Date between '20200101' and '20200220'

or the equivalent

select *
from Stores s
join Dates d
on d.Date between '20200101' and '20200220'

If each store has a different window, then use APPLY eg

select *
from Stores s
cross apply
 (
   select [Date] 
   from Dates d
   where d.[Date] between s.BeginDate and e.EndDate
 ) d