Create calendar table of next year using stored procedure in MySQL

Admin 1
Viewed 193

i need create calendar table of next year using stored procedure in MySQL.

on this code the problem is setting value of next year, because the result is

Procedure executed successfully
Affected rows: 0

I can't set next year in this part of code

'@tbl-01-01' + INTERVAL d.i * 1000 + c.i * 100 + a.i * 10 + b.i DAY AS date

how do i set the next year variable in this procedure?

any idea?

BEGIN

-- create a 2021 year
SET @tbl = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 YEAR),'%Y');

-- create a calendar table 2021
SET @s = CONCAT('DROP TABLE IF EXISTS tbl_calendar_', @tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = CONCAT('CREATE TABLE IF NOT EXISTS tbl_calendar_', @tbl, ' LIKE tbl_calendar_2020');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- create a ints table
DROP TABLE
IF EXISTS ints;

CREATE TABLE ints (i INTEGER);

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- insert into calendar table 2021 from interval day
SET @s = CONCAT('INSERT INTO tbl_calendar_', @tbl, ' SELECT
    cal.date AS cdate,
    DAY (cal.date) AS cday,
    MONTH (cal.date) AS cmonth,
    YEAR (cal.date) AS cyear, 
    NULL
FROM
    (
        SELECT
            \'`@tbl`-01-01\' + INTERVAL d.i * 1000 + c.i * 100 + a.i * 10 + b.i DAY AS date
        FROM
            ints a
        JOIN ints b
        JOIN ints c
        JOIN ints d
        ORDER BY
            d.i * 1000 + c.i * 100 + a.i * 10 + b.i
    ) cal
WHERE
    cal.date BETWEEN \'`@tbl`-01-01\'
AND \'`@tbl`-12-31\'
ORDER BY
    cal.date ASC;');  

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END
calendar stored-procedures mysql
1 Answers

You could split up the string in the concat function and have the variable outside of the string like you did in the insert clause. It would look like this:

SET @s = CONCAT('INSERT INTO tbl_calendar_', @tbl, ' SELECT
    cal.date AS cdate,
    DAY (cal.date) AS cday,
    MONTH (cal.date) AS cmonth,
    YEAR (cal.date) AS cyear, 
    NULL
FROM
    (
        SELECT \'',
            @tbl, '-01-01\' + INTERVAL d.i * 1000 + c.i * 100 + a.i * 10 + b.i DAY AS date
         ...'

You will need to split up the string in the concat function like this every time that you use the @tbl variable in the query.

user_140768730
Related