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)
#Results:
monthNumber monthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
select DateName(mm,'2012-08-27 10:10:17.327')currMonthName
#Results:
currMonthName
August
You don't need the complexity of either a While Loop or Dynamic SQL do to this.
BalasHapusThe 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, if you've got a Tally Table, the code becomes even simpler.
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/
Jeff ... thank a lot for your explanation.
BalasHapus