SQL Server 2008 R2生成月度首日：SSRS兼容替代方案问询

首先，你的问题根源在于SSRS对动态TOP子句的表达式解析限制：原查询里TOP (DATEDIFF(MONTH, @pStartDate, @pEndDate)+1)是运行时计算的值，SSRS在预检查查询时无法确认这个值的有效性，因此抛出"A TOP or FETCH clause contains an invalid value"错误。

下面提供两种无需创建函数/视图、兼容SSRS的替代写法：

方案一：调整原查询，用WHERE替代TOP #

既然master.dbo.spt_values的type='P'包含0到2047的连续数字（足够覆盖2000-2099的1200个月），我们可以直接用WHERE条件限制数字范围，避开动态TOP：

DECLARE @pStartDate DATE = '2000-01-01'
DECLARE @pEndDate DATE = '2099-12-31'

;WITH months(DATE) AS (
    SELECT DATEADD(MONTH, number, @pStartDate)
    FROM [master].dbo.spt_values
    WHERE [type] = N'P'
      AND number BETWEEN 0 AND DATEDIFF(MONTH, @pStartDate, @pEndDate)
)
SELECT CAST(DATE AS DATE) AS Date
FROM months
ORDER BY DATE

这个写法和原逻辑一致，但用number BETWEEN 0 AND 计算值代替了TOP，SSRS能正确解析这个静态范围检查。

方案二：递归CTE（更稳妥，不依赖系统表） #

如果担心系统表master.dbo.spt_values的可用性（比如某些环境限制访问），递归CTE是更可靠的选择，完全不依赖外部对象：

DECLARE @pStartDate DATE = '2000-01-01'
DECLARE @pEndDate DATE = '2099-12-31'

;WITH months(DATE) AS (
    -- 起始锚点：第一个月的首日
    SELECT @pStartDate AS DATE
    UNION ALL
    -- 递归：每次加一个月，直到超过结束日期
    SELECT DATEADD(MONTH, 1, DATE)
    FROM months
    WHERE DATEADD(MONTH, 1, DATE) <= @pEndDate
)
SELECT DATE AS Date
FROM months
ORDER BY DATE
OPTION (MAXRECURSION 1200) -- 指定递归次数，覆盖1200个月的需求

说明： #

• 递归CTE从起始日期开始，逐月生成下一个月的日期，直到超过结束日期
• OPTION (MAXRECURSION 1200)是必须的，因为SQL Server默认递归次数是100，而我们需要生成1200条记录（2000-2099共100年）

两种方案都能生成2000-01-01到2099-12-31之间每个月的首日，且在SSRS中可以正常运行。

内容的提问来源于stack exchange，提问作者NonProgrammer