SQL SERVER:选项(MAXRECURSION n)不使用动态变量

Question

技术人员，当日期范围在1/1/-1/3之间时，这或多或少有效。我有两个问题需要解决。第一个是SQL Server不允许我这样做: OPTION (MAXRECURSION @recday)。下一步，我拉取了1/4的结果，而我只想要1/3的结果。有什么建议可以让它工作吗？我尝试将查询转换为可以通过EXEC sp_executesql运行的东西。然后我遇到了一个新问题--我不能写到#StoreXJoinDate，因为这会产生另一个会话。如果我转换成##StoreXJoinDate，我不能从外部会话杀死它，因为那个会话并不拥有它。

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

下面是用于构建Dimension.Stores的insert语句

 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 1

只需使用递归CTE加载日期表。如下所示：

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)

然后，要将一个日期范围与另一个表进行交叉联接，请运行如下查询：

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

或等效的

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

如果每个商店都有不同的窗口，则使用应用，例如

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

Answer 2

而不是限制递归，简单地摆脱它，那么递归就不是问题了。此外，无论如何，计数都比rCTE快得多：

DECLARE @beginDate date;
DECLARE @endDate date;

SET @BeginDate = cast('20210101' as date);
SET @EndDate = cast('20210301' as date);

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS (
    SELECT TOP(DATEDIFF(DAY, @BeginDate, @EndDate) + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3), --1000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, @BeginDate) AS D
    FROM Tally T)
SELECT D
FROM Dates;

如果您需要超过1,000天，只需在Tally中交叉连接到N more即可。