Since it's in the article you posted a link for, let me show you that you don't need the complexity of a While Loop or Dynamic SQL.

Here's the code from the article you posted...

declare @start int,@end int

declare @qry nvarchar(100)

declare @summary nvarchar(1200)

set @start=1

set @end=13

set @summary=''

while not (@start=@end)

begin

if @start=@end-1

set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)

else

set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName

union ')qry)

set @summary=@summary+@qry

set @start=@start+1

end

exec (@summary)

The following snippet does the same thing without the loop or the dynamic SQL.

WITH

cteTally AS

(

SELECT TOP 12

N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns

)

SELECT MonthNumber = N,

MonthName = DATENAME(mm,DATEADD(mm,N,-1))

FROM cteTally

;

Of course, since this is an SQL Server 2000 forum, the code above won't work in SQL Server 2000. The following will, though... and, since it uses a Tally Table, the code becomes even more simple and works in all versions of SQL Server

.

SELECT MonthNumber = N,

MonthName = DATENAME(mm,DATEADD(mm,N,-1))

FROM dbo.Tally

WHERE N <= 12

;

For more information on what a Tally Table (or cteTally) is and how it can be used to replace certain loops in a very high performance fashion, please see the following article.

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