SQL存储过程：动态StartDate与固定今日EndDate的实现问询

Let's adjust your stored procedure to handle the dynamic start date and fixed end date requirements. Below are examples tailored to common database systems, with clear breakdowns of the key logic.

Core Logic Overview #

• Start Date: Defaults to 1/1/2018 unless the current year is 2019—then it auto-sets to 1/1/2019. We’ll use conditional checks to handle this dynamic switch.
• End Date: Always defaults to today’s date, leveraging the database’s built-in system date function.

Example for SQL Server #

CREATE OR ALTER PROCEDURE YourProcedureName
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    -- Set End Date to today if no value is provided
    SET @EndDate = ISNULL(@EndDate, GETDATE());

    -- Dynamic Start Date assignment
    SET @StartDate = ISNULL(@StartDate, 
        CASE 
            WHEN YEAR(GETDATE()) = 2019 THEN '2019-01-01'
            ELSE '2018-01-01'
        END
    );

    -- Insert your existing procedure logic here
    -- Example: SELECT * FROM YourTargetTable WHERE DateColumn BETWEEN @StartDate AND @EndDate;
END

Example for MySQL #

DELIMITER //
CREATE OR REPLACE PROCEDURE YourProcedureName(
    IN p_StartDate DATE NULL,
    IN p_EndDate DATE NULL
)
BEGIN
    -- Set End Date to today if no value is provided
    SET p_EndDate = COALESCE(p_EndDate, CURDATE());

    -- Dynamic Start Date assignment
    SET p_StartDate = COALESCE(p_StartDate,
        CASE
            WHEN YEAR(CURDATE()) = 2019 THEN '2019-01-01'
            ELSE '2018-01-01'
        END
    );

    -- Insert your existing procedure logic here
    -- Example: SELECT * FROM your_target_table WHERE date_column BETWEEN p_StartDate AND p_EndDate;
END //
DELIMITER ;

Key Details to Note #

• We use ISNULL() (SQL Server) or COALESCE() (MySQL/Oracle) to prioritize user-provided values over our dynamic defaults. This means if someone explicitly passes a start/end date, that value will be used instead of the auto-generated one.
• For Oracle, replace GETDATE()/CURDATE() with SYSDATE and adjust the procedure syntax to match Oracle’s standards.
• Stick to ISO date formats (YYYY-MM-DD) to avoid locale-based date parsing errors.

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