CODEWITHSUNDEEP
mysql
vishal
vishal

Reputation: 319

MYSQL: stored procedure date operation

I want to create stored procedure in mysql in that,i want to passed two parameters startDate in BIGINT and noOfDays in INT. So in that stored procedure create TEMPORARY table and inserts dates in that. e.g if i passed 1 aug. and 10 then in TEMPORARY table records should be from 1 aug to 10 aug. Please give suggestions for that.

Upvotes: 0

Views: 1078

Answers (1)

Omesh
Omesh

Reputation: 29101

Following procedure will do this for you:

CREATE PROCEDURE sp_insert_date_data(arg_start_date DATE, arg_no_of_days INT unsigned)
BEGIN
    SET @var_date_cnt = 0;

    DROP TEMPORARY TABLE IF EXISTS tmp_date;
    CREATE TEMPORARY TABLE tmp_date;
    (
        dates DATE NOT NULL
    );

    WHILE(@var_date_cnt < arg_no_of_days)
    DO
        INSERT INTO tmp_date(dates)
        VALUES
        (DATE_ADD(arg_start_date, INTERVAL @var_date_cnt DAY));

        SET @var_date_cnt = @var_date_cnt + 1;
    END WHILE;

    SELECT * FROM tmp_date;
END;

Upvotes: 1

Related Questions