存储过程的简单应用

CREATE TABLE CALENDAR_INFO
(
    DATE_NAME VARCHAR(20) PRIMARY KEY,
    YEAR VARCHAR(10),
    MONTH VARCHAR(10),
    WEEK VARCHAR(10),
    DAY VARCHAR(10)
)

if exists (select * from sys.procedures where name = ‘PROC_CALENDAR‘)
    drop proc PROC_CALENDAR
GO

CREATE PROC PROC_CALENDAR
@year int
AS
BEGIN
    DECLARE @i INT;
    DECLARE @start_day varchar(20);
    DECLARE @end_day varchar(20);
    DECLARE @day_count INT;
    DECLARE @today DATETIME;
    
    SET @i=0
    --定义一年的开始日期，用CONCAT函数将年份和月份日期拼接起来
    SET @start_day=CONCAT(@year,‘-01-01‘)
    --定义一年的结束日期
    SET @end_day=CONCAT(@year+1,‘-01-01‘)
    --DATEDIFF函数计算日期的间隔天数
    SET @day_count=DATEDIFF(DAY,@start_day,@end_day)
    --把@start_day转成datetime，赋值给@today
    SET @today=CONVERT(DATETIME,@start_day)

    WHILE @i< @day_count
    BEGIN    
        INSERT INTO CALENDAR_INFO VALUES(
            CONVERT(VARCHAR(10),@today,23),
            STR(@year),
            DATENAME(MONTH,@today),　　--返回月份
            DATENAME(WEEKDAY,@today), --返回星期几
            DATENAME(DAY,@today)　　　 --返回日期
        )
        SET @i=@i+1
        SET @today=CONVERT(CHAR(10),DATEADD(DAY,1,@today),23)  --天数加1
    END
END

EXEC PROC_CALENDAR 2020