如何编写存储过程生成指定年份区间的年月及当月天数

Hey Jeet, I’ve got you covered! Let’s build that stored procedure properly—whether you’re using MySQL or SQL Server, I’ll share working examples that generate exactly the data you need: year, month name, and days in each month for your specified date range.

MySQL Implementation #

This version uses loops and a temporary table to populate the data, with built-in functions to handle month names and leap years automatically:

DELIMITER //

CREATE PROCEDURE GenerateMonthDetails(IN start_year INT, IN end_year INT)
BEGIN
    -- Clean up any existing temp table
    DROP TABLE IF EXISTS temp_month_details;
    CREATE TEMPORARY TABLE temp_month_details (
        year INT,
        month_name VARCHAR(20),
        days_in_month INT
    );

    -- Loop through each year in the range
    SET @current_year = start_year;
    WHILE @current_year <= end_year DO
        -- Loop through each month (1-12)
        SET @current_month = 1;
        WHILE @current_month <= 12 DO
            -- Get the full month name (e.g., "January")
            SET @month_name = MONTHNAME(STR_TO_DATE(CONCAT(@current_year, '-', @current_month, '-01'), '%Y-%m-%d'));
            -- Calculate days in the month using LAST_DAY to handle leap years
            SET @days_in_month = DAY(LAST_DAY(STR_TO_DATE(CONCAT(@current_year, '-', @current_month, '-01'), '%Y-%m-%d')));
            
            -- Insert the row into our temp table
            INSERT INTO temp_month_details (year, month_name, days_in_month)
            VALUES (@current_year, @month_name, @days_in_month);
            
            SET @current_month = @current_month + 1;
        END WHILE;
        SET @current_year = @current_year + 1;
    END WHILE;

    -- Return the final dataset
    SELECT * FROM temp_month_details;
END //

DELIMITER ;

How to use it:

-- Generate data from 2020 to 2024
CALL GenerateMonthDetails(2020, 2024);

SQL Server Implementation #

If you’re on SQL Server, a recursive CTE is a cleaner approach without loops:

CREATE PROCEDURE GenerateMonthDetails
    @start_year INT,
    @end_year INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Recursive CTE to generate all year-month combinations in the range
    WITH YearMonths AS (
        SELECT 
            @start_year AS year,
            1 AS month_num
        UNION ALL
        SELECT 
            year + CASE WHEN month_num = 12 THEN 1 ELSE 0 END,
            CASE WHEN month_num = 12 THEN 1 ELSE month_num + 1 END
        FROM YearMonths
        WHERE year < @end_year OR (year = @end_year AND month_num < 12)
    )
    SELECT
        year,
        DATENAME(MONTH, DATEFROMPARTS(year, month_num, 1)) AS month_name,
        DAY(EOMONTH(DATEFROMPARTS(year, month_num, 1))) AS days_in_month
    FROM YearMonths
    ORDER BY year, month_num;
END;

How to use it:

-- Generate data from 2018 to 2023
EXEC GenerateMonthDetails @start_year = 2018, @end_year = 2023;

Both solutions automatically handle leap years (so February gets 29 days when needed) and correctly map month numbers to their full names. The key here is using built-in date functions (LAST_DAY/EOMONTH, MONTHNAME/DATENAME) instead of manually calculating days, which avoids errors.

内容的提问来源于stack exchange，提问作者Jeet