闲着无聊，修改后的获取日历的存储过程

 1 CREATE PROC [dbo].[GetCalendar2] ( @input DATE = NULL )
 2 AS
 3     BEGIN
 4         IF @input IS NULL
 5             SET @input = GETDATE() ;
 6                 
 7         WITH    xx ( Day, WeekDay, Week, Date )
 8                   AS ( SELECT   DAY(tt.Date) ,
 9                                 DATEPART(dw, tt.Date) ,
10                                 DATEPART(ww, tt.Date) ,
11                                 tt.Date
12                        FROM     ( SELECT    DATEADD(d, -DAY(@input) + 1,
13                                                     @input) AS Date
14                                 ) AS tt
15                        UNION ALL
16                        SELECT   xx.day + 1 ,
17                                 DATEPART(dw, DATEADD(d, 1, xx.Date)) ,
18                                 DATEPART(ww, DATEADD(d, 1, xx.Date)) ,
19                                 DATEADD(d, 1, xx.date)
20                        FROM     xx
21                        WHERE    DATEPART(m, DATEADD(d, 1, xx.Date)) = DATEPART(m,
22                                                               xx.Date)
23                      )
24             SELECT  [1] AS Sunday ,
25                     [2] AS Monday ,
26                     [3] AS Tuesday ,
27                     [4] AS Wednesday ,
28                     [5] AS Thursday ,
29                     [6] AS Friday ,
30                     [7] AS Saturday
31             FROM    ( SELECT    xx.day ,
32                                 xx.WeekDay ,
33                                 xx.Week
34                       FROM      xx
35                     ) AS p PIVOT( MAX(p.Day) FOR p.WeekDay IN ( [1], [2], [3],
36                                                               [4], [5], [6],
37                                                               [7] ) ) AS pvt
38             ORDER BY Week ASC
39     END
40 GO
41 

这里使用cte和pivot完成，和以前版本不一样，相比较以前的版本精简了代码，而且看起来更清晰一些