SQL Server存储过程实现日历显示

按年，月，用户 自动按月历形式显示数据，包含待办事项。为了方便Web页面展示和点选，增加<br>及<a>

标签等。待办事项写死TaskList表，表结构 (id,iuser,icode,idate,itatus)  icode记录待办事件文字

展示效果见图

Create proc [dbo].[iCalender]( 
@iYear int,
@iMonth int,
@iUser nvarchar(10), --取出对应用户的待办事宜
@iTable nvarchar(20)='', --预留参数，默认为空，本过程写死数据内容取自TaskList表
@iDay int=1, --周几为每周第一天,默认为周一
@iLink int=0 --是否添加<a>标签链接，默认不添加
) 
as

--按月历显示某用户的待办任务，需提供参数年、月及用户编号
--待办任务取自表TaskList (id,iuser,icode,idate,itatus)

SET DATEFIRST @iDay --设置每周第一天为周几

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tasktodolist]') AND type in (N'U'))
DROP TABLE #tasktodolist

create table #tasktodolist
(
日 nvarchar(1000),
一 nvarchar(1000),
二 nvarchar(1000),
三 nvarchar(1000),
四 nvarchar(1000),
五 nvarchar(1000),
六 nvarchar(1000)
)
declare @a table(id int identity(0,1),sdate datetime)
declare @m int, @d int

if @iMonth>=12 set @m=1 else set @m = @iMonth + 1
set @d = DAY(convert(datetime,rtrim(@iYear)+'-'+rtrim(@m)+'-1')-1)

if @d=31 
insert into @a(sdate) select top 31 rtrim(@iYear)+'-'+rtrim(@iMonth)+'-1' from sysobjects 
else if @d=30
insert into @a(sdate) select top 30 rtrim(@iYear)+'-'+rtrim(@iMonth)+'-1' from sysobjects 
else if @d=29
insert into @a(sdate) select top 29 rtrim(@iYear)+'-'+rtrim(@iMonth)+'-1' from sysobjects 
else if @d=28
insert into @a(sdate) select top 28 rtrim(@iYear)+'-'+rtrim(@iMonth)+'-1' from sysobjects

update @a set sdate=dateadd(dd,id,sdate)

insert into TaskList (iUser,iDate,iCode)
select @iuser,sdate,'' from @a where sdate not in 
(select idate from TaskList where iUser=''+@iuser+'' and YEAR(iDate)=@iyear and MONTH(iDate)=@imonth and ISNULL(istatus,0)=0)

insert into #tasktodolist
select
max(case datepart(dw,idate) when 7 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 1 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 2 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 3 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 4 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 5 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end),
max(case datepart(dw,idate) when 6 then rtrim(day(idate))+','+case when isnull(icode,'')='' then '' else left(iCode,30)+'...' end else '' end)
from
(select iDate, iCode = stuff((select ',' + CONVERT(nvarchar(10),id) + '|' + left(rTrim(icode),10) from TaskList t where iDate = TaskList.iDate and ISNULL(istatus,0)=0 and ISNULL(icode,'')<>'' and iUser='' + @iUser + '' for xml path('')) , 1 , 1 , '')
from TaskList where iUser='' + @iUser + '' and YEAR(iDate)=@iyear and ISNULL(istatus,0)=0 and MONTH(iDate)=@imonth group by iDate) as TaskList --串联字符串 默认待办事宜取自TaskList表
where
month(idate)=@iMonth and YEAR(idate)=@iYear --year=2013,month=11
group by
(case datepart(dw,idate) when 7 then datepart(week,idate)+1 else datepart(week,idate) end)

if @iLink=0
begin
update #tasktodolist --将,替换为回车换行，便于查看显示
set 日=replace(日,',','<br>'),
一=replace(一,',','<br>'),
二=replace(二,',','<br>'),
三=replace(三,',','<br>'),
四=replace(四,',','<br>'),
五=replace(五,',','<br>'),
六=replace(六,',','<br>')	
end
else 
begin
update #tasktodolist --将,替换为回车换行，便于查看显示
set 日=replace(日,',','</a><br><a href="todolist.aspx?id='),
一=replace(一,',','</a><br><a href="todolist.aspx?id='),
二=replace(二,',','</a><br><a href="todolist.aspx?id='),
三=replace(三,',','</a><br><a href="todolist.aspx?id='),
四=replace(四,',','</a><br><a href="todolist.aspx?id='),
五=replace(五,',','</a><br><a href="todolist.aspx?id='),
六=replace(六,',','</a><br><a href="todolist.aspx?id=')

update #tasktodolist 
set 日=replace(日,'|','" target="_Blank">'),
一=replace(一,'|','" target="_Blank">'),
二=replace(二,'|','" target="_Blank">'),
三=replace(三,'|','" target="_Blank">'),
四=replace(四,'|','" target="_Blank">'),
五=replace(五,'|','" target="_Blank">'),
六=replace(六,'|','" target="_Blank">')

update #tasktodolist set 
日='<a>' + 日 + '</a>',
一='<a>' + 一 + '</a>',
二='<a>' + 二 + '</a>',
三='<a>' + 三 + '</a>',
四='<a>' + 四 + '</a>',
五='<a>' + 五 + '</a>',
六='<a>' + 六 + '</a>'

update #tasktodolist 
set 日=replace(日,'<a href="todolist.aspx?id=</a>',''),
一=replace(一,'<a href="todolist.aspx?id=</a>',''),
二=replace(二,'<a href="todolist.aspx?id=</a>',''),
三=replace(三,'<a href="todolist.aspx?id=</a>',''),
四=replace(四,'<a href="todolist.aspx?id=</a>',''),
五=replace(五,'<a href="todolist.aspx?id=</a>',''),
六=replace(六,'<a href="todolist.aspx?id=</a>','') 
end

select * from #tasktodolist