SQL Server中CROSS APPLY调用ExplodeDates函数时查询无限运行

嘿，我来帮你搞定这个日期拆分函数的问题！你提到的ExplodeDates函数没法正常跑，而且在本地SQL Server 2016和Azure空库都能复现，大概率是函数代码不完整或者调用时的小问题导致的，咱们一步步来解决。

首先，先把完整的ExplodeDates函数代码补全（你的代码截断到N1...，这会直接导致函数创建失败），这是这类日期生成函数的典型完整实现：

/* FUNCTION: [dbo].[ExplodeDates] */
DROP FUNCTION IF EXISTS [dbo].[ExplodeDates]
GO
CREATE FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime)
returns table as
return (
    with N0 as (SELECT 1 as n UNION ALL SELECT 1)
        ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
        ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
        ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
        ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
        ,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
        ,Numbers as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as num FROM N5)
    SELECT DATEADD(day, num-1, @startdate) as DateValue
    FROM Numbers
    WHERE num <= DATEDIFF(day, @startdate, @enddate) + 1
)
GO

接下来，咱们排查可能的问题点：

• 代码不完整：你之前提供的代码截断了CTE链，N1之后的层级没写完，这会让SQL Server无法解析函数，直接创建失败。用上面的完整代码重新创建函数试试。
• 权限不足：不管是本地SQL Server还是Azure SQL数据库，创建函数都需要对应的权限。本地库确保你有数据库的CREATE FUNCTION权限，Azure SQL的话，建议你用db_ddladmin角色的用户来操作。
• 参数顺序错误：如果调用时把@startdate和@enddate搞反了（比如开始日期比结束日期晚），函数会返回空结果，看起来像是“无法运行”。可以给函数加个参数校验，自动处理这种情况：
  修改后的函数会自动交换日期顺序，避免空结果：

  DROP FUNCTION IF EXISTS [dbo].[ExplodeDates]
  GO
  CREATE FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime)
  returns table as
  return (
      with N0 as (SELECT 1 as n UNION ALL SELECT 1)
          ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
          ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
          ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
          ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
          ,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
          ,Numbers as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as num FROM N5)
      SELECT DATEADD(day, num-1, CASE WHEN @startdate > @enddate THEN @enddate ELSE @startdate END) as DateValue
      FROM Numbers
      WHERE num <= DATEDIFF(day, CASE WHEN @startdate > @enddate THEN @enddate ELSE @startdate END, CASE WHEN @startdate > @enddate THEN @startdate ELSE @enddate END) + 1
  )
  GO
  

• 测试查询错误：用下面的简单查询验证函数是否正常工作：

  -- 生成2024年1月1日到1月5日的所有日期
  SELECT * FROM dbo.ExplodeDates('2024-01-01', '2024-01-05')
  

按照上面的步骤操作，应该就能解决你的问题了。

内容的提问来源于stack exchange，提问作者Daniel C.