每個月的sql引數配置

select * from dbo.calendar(7,2009)

create function calendar(@month int,@year int)
returns @DAY1 table(Sun char(3),Mon Char(3),Tue Char(3),Wed Char(3),Thu Char(3),Fri Char(3),Sat Char(3))
as
begin
declare @i int
declare @j int
declare @intchk int
declare @dnum int
declare @curdate int
declare @month1 char(2)
declare @year1 char(4)
declare @date char(2)
declare @dtchk int
declare @dtval int
set @date='01'
set @month1=@month
set @year1=@year
set @dtchk=1
set @i=1
set @j=1


declare @DAY2 table(SUN char(3) default '',MON Char(3)default '',TUE Char(3)default '',WED Char(3)default '',THU Char(3)default '',FRI Char(3)default '',SAT Char(3)default '')

SELECT @curdate=DATEPART(dw, CONVERT(DATETIME,@date+'-'+@month1+'-'+@year1,103))

select @dnum=datediff(dd,convert(datetime,@date+'-'+@month1+'-'+@year1,103),dateadd(mm,1,convert(datetime,@date+'-'+@month1+'-'+@year1,103)))


while @j<=7
begin
if @curdate=@j
begin
if @j=1
begin
INSERT INTO @DAY2(sun)VALUES(@i)
set @intchk=1
set @dtchk=1
end
else if @j=2
begin
INSERT INTO @DAY2(MON)VALUES(@i)
set @intchk=2
set @dtchk=1
end
else if @j=3
begin
INSERT INTO @DAY2(TUE)VALUES(@i)
set @intchk=3
set @dtchk=1
end
else if @j=4
begin
INSERT INTO @DAY2(WED)VALUES(@i)
set @intchk=4
set @dtchk=1
end
else if @j=5
begin
INSERT INTO @DAY2(THU)VALUES(@i)
set @intchk=5
set @dtchk=1
end
else if @j=6
begin
INSERT INTO @DAY2(FRI)VALUES(@i)
set @intchk=6
set @dtchk=1
end
else if @j=7
begin
INSERT INTO @DAY2(SAT)VALUES(@i)
set @intchk=7
set @dtchk=1
end

end
set @j=@j+1
end
if @intchk=1
begin
update @day2 set mon=@i+1,tue=@i+2,wed=@i+3,thu=@i+4,fri=@i+5,sat=@i+6 where sun=1
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set tue=@i+1,wed=@i+2,thu=@i+3,fri=@i+4,sat=@i+5 where mon=1
set @dtchk=@dtchk+5
end
else if @intchk=3
begin
update @day2 set wed=@i+1,thu=@i+2,fri=@i+3,sat=@i+4 where tue=1
set @dtchk=@dtchk+4
end
else if @intchk=4
begin
update @day2 set thu=@i+1,fri=@i+2,sat=@i+3 where wed=1
set @dtchk=@dtchk+3
end
else if @intchk=5
begin
update @day2 set fri=@i+1,sat=@i+2 where thu=1
set @dtchk=@dtchk+2
end
else if @intchk=6
begin
update @day2 set sat=@i+1 where fri=1
set @dtchk=@dtchk+1
end

else if @intchk=7
begin
Set @dtchk=@dtchk
end



insert into @day2(sun)values(@dtchk+1)
set @dtchk=@dtchk+1
if @intchk=1
begin
update @day2 set mon=@i+8,tue=@i+9,wed=@i+10,thu=@i+11,fri=@i+12,sat=@i+13 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set mon=@i+7,tue=@i+8,wed=@i+9,thu=@i+10,fri=@i+11,sat=@i+12 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=3
begin
update @day2 set mon=@i+6,tue=@i+7,wed=@i+8,thu=@i+9,fri=@i+10,sat=@i+11 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=4
begin
update @day2 set mon=@i+5,tue=@i+6,wed=@i+7,thu=@i+8,fri=@i+9,sat=@i+10 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=5
begin
update @day2 set mon=@i+4,tue=@i+5,wed=@i+6,thu=@i+7,fri=@i+8,sat=@i+9 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=6
begin
update @day2 set mon=@i+3,tue=@i+4,wed=@i+5,thu=@i+6,fri=@i+7,sat=@i+8 where sun=@dtchk
set @dtchk=@dtchk+6
end

else if @intchk=7
begin
update @day2 set mon=@i+2,tue=@i+3,wed=@i+4,thu=@i+5,fri=@i+6,sat=@i+7 where sun=@dtchk
set @dtchk=@dtchk+6
end
insert into @day2(sun)values(@dtchk+1)
set @dtchk=@dtchk+1
if @intchk=1
begin
update @day2 set mon=@i+15,tue=@i+16,wed=@i+17,thu=@i+18,fri=@i+19,sat=@i+20 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set mon=@i+14,tue=@i+15,wed=@i+16,thu=@i+17,fri=@i+18,sat=@i+19 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=3
begin
update @day2 set mon=@i+13,tue=@i+14,wed=@i+15,thu=@i+16,fri=@i+17,sat=@i+18 where sun=@dtchk

set @dtchk=@dtchk+6
end
else if @intchk=4
begin
update @day2 set mon=@i+12,tue=@i+13,wed=@i+14,thu=@i+15,fri=@i+16,sat=@i+17 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=5
begin
update @day2 set mon=@i+11,tue=@i+12,wed=@i+13,thu=@i+14,fri=@i+15,sat=@i+16 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=6
begin
update @day2 set mon=@i+10,tue=@i+11,wed=@i+12,thu=@i+13,fri=@i+14,sat=@i+15 where sun=@dtchk
set @dtchk=@dtchk+6
end

else if @intchk=7
begin
update @day2 set mon=@i+9,tue=@i+10,wed=@i+11,thu=@i+12,fri=@i+13,sat=@i+14 where sun=@dtchk
set @dtchk=@dtchk+6
end
insert into @day2(sun)values(@dtchk+1)
set @dtchk=@dtchk+1

if @intchk=1
begin
update @day2 set mon=@i+22,tue=@i+23,wed=@i+24,thu=@i+25,fri=@i+26,sat=@i+27 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set mon=@i+21,tue=@i+22,wed=@i+23,thu=@i+24,fri=@i+25,sat=@i+26 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=3
begin
update @day2 set mon=@i+20,tue=@i+21,wed=@i+22,thu=@i+23,fri=@i+24,sat=@i+25 where sun=@dtchk

set @dtchk=@dtchk+6
end
else if @intchk=4
begin
update @day2 set mon=@i+19,tue=@i+20,wed=@i+21,thu=@i+22,fri=@i+23,sat=@i+24 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=5
begin
update @day2 set mon=@i+18,tue=@i+19,wed=@i+20,thu=@i+21,fri=@i+22,sat=@i+23 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=6
begin
update @day2 set mon=@i+17,tue=@i+18,wed=@i+19,thu=@i+20,fri=@i+21,sat=@i+22 where sun=@dtchk
set @dtchk=@dtchk+6
end

else if @intchk=7
begin
update @day2 set mon=@i+16,tue=@i+17,wed=@i+18,thu=@i+19,fri=@i+20,sat=@i+21 where sun=@dtchk
set @dtchk=@dtchk+6
end
/*set @i=@i+1
end*/



if @dtchk<>@dnum
begin
set @dtchk=@dtchk+1
insert into @day2(sun)values(@dtchk)
set @dtval=@dtchk

set @dtchk=@dtchk+1
if @dtchk<=@dnum
begin
update @day2 set mon =@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end
/*insert into @dd(jk1,jk2,jk3)values(@dtchk,@dtval,@dnum)*/
if @dtchk<=@dnum
begin
update @day2 set tue=@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end

if @dtchk<=@dnum
begin
update @day2 set wed=@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end

if @dtchk<=@dnum
begin
update @day2 set thu=@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end

if @dtchk<=@dnum
begin
update @day2 set fri=@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end

if @dtchk<=@dnum
begin
update @day2 set sat=@dtchk where sun=@dtval
set @dtchk=@dtchk+1
end
if @dtchk<=@dnum
begin
insert into @day2(sun)values(@dtchk)
set @dtval=@dtchk
set @dtchk=@dtchk+1
if @dtchk<=@dnum
begin
update @day2 set mon=@dtchk where sun=@dtval
end
end

end
insert into @DAY1 select * from @DAY2

return
end

[@more@]