SQL stored procedure: increment months from a starting date to an end date

Question

I'm trying to create a stored procedure in SQL Server that basically increments the months from a given start date up to a given end date and updates them into a predefined table.

Problem: Somewhat like this:

Exec MonthRunner @Start ='2014-01-01', @End = '2014-06-01'

Should give me a table like this:

Date 
2014-01-01
2014-02-01
2015-03-01
2015-04-01
2015-05-01
2015-06-01

Approach: A loop programmed using a cursor. Something similar to this:

Create Procedure MonthRunner(@Start date, @End date)
AS
DECLARE @Date date
DECLARE @getid CURSOR


SET @getid = CURSOR FOR
Select (@Start)


Set @getid 
OPEN @getid 
FETCH NEXT
FROM @getid into @Date


WHILE @@FETCH_STATUS = 0
BEGIN
SET @Date = (Select Dateadd(Month, 1, @Date))
DECLARE @sqlrun varchar(max)

SET @sqlrun= 'Update myTable' + 'Set Date=' + @Date
EXEC @sqlrun


fetch next 
from @getid into @Date
END

CLOSE @getid
DEALLOCATE @getid

So far my result is:

Update myTable Set Date='2014-02-01'

But there is no loop and also I don't know how to terminate the loop using the variable @End.

Your help would be greatly appreciated! Thanks in advance,

Clemens

Answer 1

This can easily be done with a recursive CTE:

;WITH cte AS (
    SELECT      @Start      AS [Month]
    UNION ALL
    SELECT      DATEADD(MONTH, 1, [Month])
    FROM        cte
    WHERE       [Month] < @End
)

SELECT  [Month]
FROM    cte
OPTION  (MAXRECURSION 0)