DROP PROC PROC_Year_Month_All_Date
go
create PROC PROC_Year_Month_All_Date(
@Year int,
@Month int,
@Msg varchar(30) output
)
as
BEGIN TRANSACTION begin
declare @i datetime
set @i= DateAdd(dd, 0, Cast(ltrim(str(@Year)) + '-' +
ltrim(str(@Month)) + '-' + '01' As DateTime))
DECLARE @Month_All_Date TABLE ([id] int identity(1,1),日期 datetime,星期 char(6))
while(datepart(yy,@i)=@Year AND datepart(mm,@i)=@Month)
begin
insert into @Month_All_Date(日期,星期)
values(CONVERT(VARCHAR(10),@i,120),datename(weekday,CONVERT(VARCHAR(10),@i,120)))
set @i=dateadd(d,1,@i)
end
select * from @Month_All_Date
end
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION SET @MSG='获取<<'+ltrim(str(@Year))+'年'+ltrim(str(@Month))+'月>>'+'日期失败?' SELECT @MSG RETURN 1 ENDELSE BEGIN COMMIT TRANSACTION SET @MSG='获取<<'+ltrim(str(@Year))+'年'+ltrim(str(@Month))+'月>>'+'日期成功!' SELECT @MSG RETURN 0 END
--测试存储过程
Declare @Year int, @Month Int,@Msg varchar(30)
Select @Year = 2011, @Month =2
execute PROC_Year_Month_All_Date @Year,@Month,@Msg output
--执行结果下载本文
