MySQL: Stored procedure with variable DATE_ADD intervals

Question

I'm trying to write a MySQL sproc that will copy certain table rows based on the value of one field, rwo_frequency. For example, if rwo_frequency = 2, then the date interval is one week, and my statement would look like this:

INSERT INTO table (start_date, <other columns>)
SELECT DATE_ADD(start_date, INTERVAL 7 DAY) AS start_date, <other columns>
FROM table
WHERE start_date = DATE_ADD(CURDATE(), INTERVAL -7 DAY)
AND rwo_frequency = 2;

So, if start_date for a record = '2021-06-04', then that record will be copied on 2021-06-11 and given that date as the start date.

Where I'm stuck is how to express rwo_frequency and the time interval for DATE_ADD as variables so that the sproc covers all values for rwo_frequency. So, if the frequency were monthly (rwo_frequency = 3), then the DATE_ADD interval would automatically be 'INTERVAL 1 MONTH'. How can I achieve this in the body of my sproc?

Bill Karwin · Accepted Answer · 2021-06-11 14:04:13Z

3

You can use a CASE expression to make the right side of that comparison a different value for each respective value of rwo_frequency.

INSERT INTO table (start_date, <other columns>)
SELECT DATE_ADD(start_date, INTERVAL 7 DAY) AS start_date, <other columns>
FROM table
WHERE start_date = CASE rwo_frequency
    WHEN 2 THEN DATE_ADD(CURDATE(), INTERVAL -7 DAY)
    WHEN 3 THEN DATE_ADD(CURDATE(), INTERVAL -1 MONTH) 
    ...
    END

If rwo_frequency does not match any of the cases, and you don't write an ELSE case, then result of the whole expressions is NULL.

answered Jun 11, 2021 at 14:04

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Thanks. This works in both places where I have to do a DATE_ADD. Great solution.

Collectives™ on Stack Overflow

MySQL: Stored procedure with variable DATE_ADD intervals

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related