使用递归CTE的一种方法：
declare @calYear int = 5, @currentYear char(4) = '2014';with cte (dt) as ( select DATEFROMPARTS(@currentyear,1,1) dt union all select dateadd(month,1,dt) from cte where dt < dateadd(year,@calyear,DATEFROMPARTS(@currentyear,1,1)) )select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth from cteorder by dt
或使用数字表：（在这种情况下为master..spt_values）
declare @calYear int = 5, @currentYear char(4) = '2014';with cte2 (dt) as ( select dateadd(month,number,DATEFROMPARTS(@currentyear,1,1)) dt from master..spt_values where type = 'p' and number <= 12*@calYear )select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth from cte2order by dt
