使用递归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 cte
order 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 cte2
order by dt
602392714
清零编程群