Hey, that's great, but only if performance is not important to you 🙂

For a good explanation of why RBAR techniques like this can really suck, see MVP Jeff Moden's article[/url] on this site.

For comparison, let's do the same thing, but in a set-based manner:

-- For demonstration purposes only

USE tempdb;

GO

-- Conditional object drops

IF OBJECT_ID(N'dbo.MonthYearList')

IS NOT NULL

DROP FUNCTION dbo.MonthYearList;

GO

IF OBJECT_ID(N'dbo.Numbers')

IS NOT NULL

DROP FUNCTION dbo.Numbers;

GO

-- An in-line function to create a numbers table on the fly

CREATE FUNCTION dbo.Numbers

(@N BIGINT)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

WITH

N1 AS (SELECT N = 1 UNION ALL SELECT 1),

N2 AS (SELECT N = 1 FROM N1 T, N1),

N3 AS (SELECT N = 1 FROM N2 T, N2),

N4 AS (SELECT N = 1 FROM N3 T, N3),

N5 AS (SELECT N = 1 FROM N4 T, N4),

N6 AS (SELECT N = 1 FROM N5 T, N5),

NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)

SELECT N

FROM NM

WHERE @N >= 0

AND N <= @N

GO

-- An in-line function wrapping the set-based implementation

CREATE FUNCTION dbo.MonthYearList

(@From DATETIME)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT the_year = YEAR(This.month_start),

the_month = MONTH(This.month_start)

FROM dbo.Numbers (DATEDIFF(MONTH, @From, CURRENT_TIMESTAMP) + 1)

CROSS

APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) Base (month_start)

CROSS

APPLY (SELECT DATEADD(MONTH, 1 - N, Base.month_start)) This (month_start)

WHERE This.month_start BETWEEN @From AND CURRENT_TIMESTAMP;

GO

-- Collect performance statistics

SET STATISTICS IO, TIME ON;

-- The RBAR method

WITH mycte AS (SELECT cast ('1753-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +

1 < getdate())

SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth

from mycte OPTION ( MAXRECURSION 0 )

-- Set based

SELECT *

FROM dbo.MonthYearList ('17530101');

-- Stop recording statistics

SET STATISTICS IO, TIME OFF;

GO

-- Tidy up

DROP FUNCTION dbo.MonthYearList;

DROP FUNCTION dbo.Numbers;

Performance results:

Your method: 2152ms

Set-based: 168ms