t-sql select get all Months within a range of years

Gosh folks... using a "counting recursive CTE" or "rCTE" is as bad or worse than using a loop. Please see the following article for why I say that.

http://www.sqlservercentral.com/articles/T-SQL/74118/

Here's one way to do it without any RBAR including the "hidden RBAR" of a counting rCTE.

--===== Declare and preset some obviously named variables
DECLARE @StartDate DATETIME,
        @EndDate   DATETIME
;
 SELECT @StartDate = '2010-01-14', --We'll get the month for both of these 
        @EndDate   = '2020-12-05'  --dates and everything in between
;
WITH
cteDates AS
(--==== Creates a "Tally Table" structure for months to add to start date
     -- calulated by the difference in months between the start and end date.
     -- Then adds those numbers to the start of the month of the start date.
 SELECT TOP (DATEDIFF(mm,@StartDate,@EndDate) + 1)
        MonthDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate) 
                  + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
--===== Slice each "whole month" date into the desired display values.
 SELECT [Year]  = YEAR(MonthDate),
        [Month] = MONTH(MonthDate) 
   FROM cteDates
;

You can use something like this: http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301.aspx

To generate the equivalent of a numbers table using date ranges.

But could you please clarify your inputs and outputs?

Do you want to input a start date, for example, '2010-5-1' and end date, for example, '2010-8-1' and have it return every month between the two? Do you want to include the start month and end month, or exclude them?

Here's some code that I wrote that will quickly generate an inclusive result of every month between two dates.

--Inputs here:
DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
SET @StartDate = '2010-1-5 5:00PM';
SET @EndDate = GETDATE();

--Procedure here:
  WITH RecursiveRowGenerator (Row#, Iteration) AS (
       SELECT 1, 1
        UNION ALL
       SELECT Row# + Iteration, Iteration * 2
         FROM RecursiveRowGenerator
        WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1))
        UNION ALL
       SELECT Row# + (Iteration * 2), Iteration * 2
         FROM RecursiveRowGenerator
        WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1))
     )
     , SqrtNRows AS (
       SELECT *
         FROM RecursiveRowGenerator
        UNION ALL
       SELECT 0, 0
     )
SELECT TOP(DATEDIFF(MONTH, @StartDate, @EndDate)+1) 
       DATEADD(month, DATEDIFF(month, 0, @StartDate) + A.Row# * POWER(2,CEILING(LOG(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1))/LOG(2))) + B.Row#, 0)  Row#
  FROM SqrtNRows A, SqrtNRows B
 ORDER BY A.Row#, B.Row#;

Code below generates the values for the range between 21 Jul 2013 and 15 Jan 2014. I usually use it in SSRS reports for generating lookup values for the Month parameter.

declare
    @from date = '20130721',
    @to date = '20140115';

with m as (
select * from (values ('Jan', '01'), ('Feb', '02'),('Mar', '03'),('Apr', '04'),('May', '05'),('Jun', '06'),('Jul', '07'),('Aug', '08'),('Sep', '09'),('Oct', '10'),('Nov', '11'),('Dec', '12')) as t(v, c)),

y as (select cast(YEAR(getdate()) as nvarchar(4)) [v] union all select cast(YEAR(getdate())-1 as nvarchar(4)))

select m.v + ' ' + y.v [value_field], y.v + m.c [label_field]
from m
cross join y
where y.v + m.c between left(convert(nvarchar, @from, 112),6) and left(convert(nvarchar, @to, 112),6)
order by y.v + m.c desc

Results:

value_field     label_field
---------------------------
Jan 2014        201401
Dec 2013        201312
Nov 2013        201311
Oct 2013        201310
Sep 2013        201309
Aug 2013        201308
Jul 2013        201307